Oracle has provided views to assist in managing the SGA and PGA settings when you’re not using Automatic Memory Management (AMM) and a view to manage memory_target when you
Starting with 10gR2 Oracle provided V$SGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE to assist the DBA in sizing the SGA and PGA based on how the database was using memory. The definitions of these views (as of 11.2.0.4) are shown below:
SQL> desc v$sga_target_advice
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
SGA_SIZE NUMBER
SGA_SIZE_FACTOR NUMBER
ESTD_DB_TIME NUMBER
ESTD_DB_TIME_FACTOR NUMBER
ESTD_PHYSICAL_READS NUMBER
SQL> desc v$pga_target_advice
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
PGA_TARGET_FOR_ESTIMATE NUMBER
PGA_TARGET_FACTOR NUMBER
ADVICE_STATUS VARCHAR2(3)
BYTES_PROCESSED NUMBER
ESTD_TIME NUMBER
ESTD_EXTRA_BYTES_RW NUMBER
ESTD_PGA_CACHE_HIT_PERCENTAGE NUMBER
ESTD_OVERALLOC_COUNT NUMBER
SQL>
Nothing unusual or esoteric here, however it appears that interpreting the advice can be confusing. Looking first at the contents of V$SGA_TARGET_ADVICE let’s see what Oracle reports:
SQL> select * From v$sga_target_advice
2 /
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
520 .5 65793 5.8208 2579079
780 .75 23911 2.1155 2068005
1040 1 11303 1 1134459
1300 1.25 9162 .8106 970643
1560 1.5 9098 .8049 970643
1820 1.75 9058 .8014 970643
2080 2 9058 .8014 970643
7 rows selected.
SQL>
Based on resource usage since startup the view provides information on memory settings that may improve performance if adopted. The key element in any of these views is the size factor, with a size factor of 1 being the current setting. Looking at the above output it’s possible that physical reads can be reduced a small amount if sga_target is increased from 1040 MB to 1300 MB; any further increase to sga_target will show no improvement. This is proven by the ESTD_PHYSICAL_READS column, the value of which stays constant for an SGA sized at 1300 MB or greater. In addition the ESTD_DB_TIME_FACTOR doesn’t improve much at greater SGA sizes indicating that further increases provide no real benefit. Ideally the goal is to find the setting providing substantial benefit and use it; the problem appears to be ‘where do I stop looking?’ With the above output we stopped looking for improvement at the
If sga_target and sga_max_size are set differently (to allow some degree of dynamic SGA management) it may not be necessary to restart the database since sga_target may be able to be set without adjusting sga_max_size. This view may, however, provide an SGA setting that requires both sga_target and sga_max_size be adjusted, and in that case the database will need to be restarted.
In a similar fashion the V$PGA_TARGET_ADVICE view can also be used to ‘fine tune’ overall PGA settings for the database:
SQL> select * From v$pga_target_advice;
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
73400320 .125 ON 7.4905E+10 37173880 1395450880 98 88
146800640 .25 ON 7.4905E+10 36716537 456747008 99 6
293601280 .5 ON 7.4905E+10 36643991 307845120 100 0
440401920 .75 ON 7.4905E+10 36494007 0 100 0
587202560 1 ON 7.4905E+10 36494007 0 100 0
704643072 1.2 ON 7.4905E+10 36494007 0 100 0
822083584 1.4 ON 7.4905E+10 36494007 0 100 0
939524096 1.6 ON 7.4905E+10 36494007 0 100 0
1056964608 1.8 ON 7.4905E+10 36494007 0 100 0
1174405120 2 ON 7.4905E+10 36494007 0 100 0
1761607680 3 ON 7.4905E+10 36494007 0 100 0
PGA_TARGET_FOR_ESTIMATE PGA_TARGET_FACTOR ADV BYTES_PROCESSED ESTD_TIME ESTD_EXTRA_BYTES_RW ESTD_PGA_CACHE_HIT_PERCENTAGE ESTD_OVERALLOC_COUNT
----------------------- ----------------- --- --------------- ---------- ------------------- ----------------------------- --------------------
2348810240 4 ON 7.4905E+10 36494007 0 100 0
3523215360 6 ON 7.4905E+10 36494007 0 100 0
4697620480 8 ON 7.4905E+10 36494007 0 100 0
14 rows selected.
SQL>
Again it’s the target factor that identifies the current setting; working toward larger target factors may show improvement in the PGA cache hit percentage, which means processes will see more optimized (one-pass) reads. Like the V$SGA_TARGET_ADVICE view the method is to find a setting that provides improvement. In this example the PGA setting is already optimal (the cache hit percentage is 100 and 0 extra bytes will need to be read). Not every database will be so lucky; this view is provided to allow the DBA to make as few changes as possible to achieve improved performance and throughput, eliminating the need for trial and error PGA management. In this case the database will need to be restarted after the parameter values have been changed.
To restate the methodology of using these two views:
1. Find the row referencing the current setting; this will be the row where the size factor is equal to 1
2. Scan the data for the entry just before the improvement estimates no longer change; this will be the setting
Oracle has calculated to provide the most improvement
3. Implement that setting, restarting the database, if necessary, to effect the change
In a similar fashion when using Automatic Memory Management, by setting memory_target and memory_max_target, the V$MEMORY_TARGET_ADVICE view can be used to tune the overall memory settings. Output from this view looks like this:
SQL> select * From v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION CON_ID
----------- ------------------ ------------ ------------------- ---------- ----------
392 .25 143586 1.0063 0 0
784 .5 142687 1 0 0
1176 .75 142687 1 0 0
1568 1 142687 1 0 0
1960 1.25 142687 1 0 0
2352 1.5 142687 1 0 0
2744 1.75 142687 1 0 0
3136 2 142687 1 0 0
8 rows selected.
SQL>
The same methodology is used here, and if memory_max_target is larger than the suggested memory_target value the setting can be changed without restarting the database. Like the SGA settings if memory_max_target needs to also be adjusted this will require a database restart. Note that if you’re using Linux and hugepages using AMM is not supported.
Managing database memory settings doesn’t need to be a grueling task of ‘try this, see if it works, it doesn’t so try another setting and repeat’. Using the provided memory advisors can make the DBA’s job much easier. I hope this has made
