What is the view V$SGA_TARGET_ADVICE
The V$SGA_TARGET_ADVICE view provides information that helps you decide on a value for SGA_TARGET. It is new to Oracle10g, and is part of the implementation of Automatic Shared Memory Management (ASMM) of Oracle10g and higher releases. As the database is used, the MMON process gather statistics and updates the view. It can then be queried to determine optimal values for the SGA_TARGET parameter.
PLEASE NOTE: For the view to be populated, DB_CACHE_ADVICE should be ON (default) and STATISTICS_LEVEL should be TYPICAL (default).
How are the results interpreted?
On a newly opened database, the view will look like this:
SQL> select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ——— ————— ———— ——————- ——————- 290 .5 34 1 1949 435 .75 34 1 1949 580 1 34 1 1949 725 1.25 34 1 1949 870 1.5 34 1 1949 1015 1.75 34 1 1949 1160 2 34 1 1949
Note that the SGA_SIZE column is in units of megabytes.
As the database is used over time, ASMM will perform re-size operations, and the MMON process will gather statistics and update the data that the view uses:
SQL> select * from v$sga_target_advice order by sga_size;
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ——— ————— ———— ——————- ——————- 290 .5 448176 1.6578 1636103 435 .75 339336 1.2552 1636103 580 1 270344 1 1201780 725 1.25 239038 .8842 907584 870 1.5 211517 .7824 513881 1015 1.75 201866 .7467 513881 1160 2 200703 .7424 513881
After the database has been running for a period of time that is representative of a typical load, re-run the query.
Determine if the SGA_TARGET is already at the SGA_SIZE with the lowest ESTD_DB_TIME and/or ESTD_PHYSICAL_READS. If not, then to get the best performance from the SGA, the SGA_TARGET should be changed to the optimal SGA_SIZE.
Using the results above, we see that the smallest ESTD_PHYSICAL_READS is 513881, and the smallest ESTD_DB_TIME is 200703. However, the biggest improvement appears to be at SGA_SIZE_FACTOR of 1.5, with any further increase in SGA_SIZE_FACTOR show diminishing returns. So, using the results above, increasing the SGA_TARGET from 580Mb to 870Mb would give the best performance without allocating more memory than may be necessary.
Using Oracle10g
If SGA_MAX_SIZE is set, and it is greater than or equal to the optimal SGA_SIZE, then make the change dynamically:
SQL> ALTER SYSTEM SET SGA_TARGET=new-value SCOPE=BOTH;
Otherwise, use SCOPE=SPFILE and re-start the instance, so the change takes effect:
SQL> ALTER SYSTEM SET SGA_TARGET=new-value SCOPE=SPFILE;
Using Oracle11g or higher releases
In Oracle11g (or higher), the information in the V$SGA_TARGET_ADVICE view is similar to that provided in the V$MEMORY_TARGET_ADVICE view for Automatic Shared Memory Management.
If MEMORY_TARGET is set, then SGA_TARGET should be set to 0. See Document 443746.1 for more details on this.
If MEMORY_TARGET is not set, use the same steps as for 10g.
Comments