memory advisor

10g em中给出了memory advisor，其中如果设置了sga_target的值的话，那么该顾问通过图表的形式给出的是sga的建议值，该建议值是如何被计算的呢，其实是通过下面公式：

select sga_size,100* round(((estd_db_time1 - estd_db_time)/estd_db_time1),2)
from (
select sga_size,estd_db_time ,(select estd_db_time from v$sga_target_advice where sga_size_factor=1) estd_db_time1 from v$sga_target_advice where sga_size_factor<>1
order by sga_size
)

[@more@]

通过上面查询可以获得sga_size以及db_time的改善百分比，在excel中以sga_size为横坐标，以"db_time的改善百分比"为纵坐标画一副折线图就是em中给出的图形。

http://download.oracle.com/docs/cd/B19306_01/server.102/b14196/montune003.htm#sthref790Using the Memory Advisor
Adequate physical memory has a significant impact on the performance of your Oracle Database. The SGA and PGA target initialization parameters determine the amount of physical memory available to the database. When you use the DBCA to create your database, the initial value of these parameters is configured according to your workload type and the total amount of memory available on your machine.

With Oracle's automatic memory management capabilities, Oracle automatically adjusts the memory distribution among the various SGA and PGA sub-components for optimal performance. These adjustments are made within the boundaries of your total SGA and PGA target values.

ADDM periodically evaluates the performance of your database to determine performance bottlenecks. If ADDM finds that the current amount of available memory is inadequate and adversely affecting performance, it can recommend that you increase your SGA or PGA target value. You can set new values for the SGA and PGA using the Memory Advisor.

Additionally, you can use the Memory Advisor to perform what-if analysis on:

The database performance benefits of adding physical memory to your database.

The database performance impact of reducing the physical memory available to your database.

Optionally, you can use the Memory Advisor to set a new value for the SGA and PGA targets, based on what-if analysis.

See Also:

For more information about memory configuration, see Oracle Database Performance Tuning GuideSetting New SGA or PGA Targets
As a response to an ADDM performance finding, you can use the Memory Advisor to set a new SGA or PGA target. In the user-interface, these parameters correspond to Total SGA Size and Aggregate PGA Target.

To set a new SGA target:

Navigate to the memory advisor in one of the following ways:

From the Home Page, under related links, click Advisor Central, then Click Memory Advisor.

From the Administration page, under Database Configuration, click Memory Parameters.

The Memory Parameters: SGA page appears. This page gives the breakdown of memory allocation for the system global area (SGA) and its subcomponents such as the buffer cache and shared pool. For more information about these components, see "Instance Memory Structure" in Chapter 5, "Managing the Oracle Instance".

To set a new SGA size, enter the new amount for Total SGA Size. The new value must be less than the maximum SGA. Click Apply. A confirmation message appears.

Similarly, to set a new PGA size:

Navigate to the PGA property page. This page shows the current value for the Aggregate PGA Target, as well as the current allocation of PGA memory. For more information about the PGA, see "Instance Memory Structure" in Chapter 5, "Managing the Oracle Instance".

Enter the new amount next to Aggregate PGA Target. Click Apply. A confirmation message appears.
Determining Performance Impact for Altering your SGA or PGA
You can use the Memory Advisor to do what-if analysis for adding or removing physical memory to your database. This advisor graphically analyses the database performance impact of altering your SGA or PGA targets.

To gain advice on configuring the total size of your SGA, on the Memory Parameters: SGA Page, click Advice next to Total SGA Size.

The SGA Advice graph appears.

Improvement in DB Time (%) is plotted against Total SGA size. A higher number for Improvement in DB Time is better for performance. In this example, the graph tells us that an SGA size larger than 1000MB will not improve performance by much. Thus 1000MB is the recommended optimal SGA size.

Similarly, to run the PGA advisor, navigate to the PGA property page. Next to Aggregate PGA Target, click Advice. The PGA Target Advice Page appears, plotting Cache hit percentage against PGA Target memory size.

Cache hit percentage is the percentage of read requests serviced by memory, as opposed to those requests serviced by reading from disk, which is slower. A higher hit percentage indicates better cache performance. The optimal zone for cache hit percentage is between 75 and 100 percent. However, it is not safe to conclude that your database is having performance problems simply because your cache hit percentage is not within the optimal zone. When the amount of currently available PGA memory is not adequate for optimal performance, ADDM will automatically recommend adjusting this value with a performance finding.

--============================

SELECT sga_size, (1-estd_db_time_factor)*100 FROM v$sga_target_advice order by 1

其实在后来我无意中在top sql中发现其实oracle是通过上面公式计算sga_target advisor的

转自：http://warehouse.itpub.net/post/777/475147

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/789833/viewspace-1046986/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/789833/viewspace-1046986/