The V$SGA_TARGET_ADVICE view provides information that helps us in deciding optimal value for SGA_TARGET.
MMON background process gather statistics about sga_target usage and update the V$SGA_TARGET_ADVICE view.
Check sga target advisory is enable?
--- DB_CACHE_ADVICE should be ON SQL> show parameter db_cache_advice NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------ db_cache_advice string ON -- STATISTICS_LEVEL should be TYPICAL/ALL. SQL> show parameter statistics_level NAME TYPE VALUE ------------------------------------ -------------------------------- -------------------------- statistics_level string TYPICAL
SGA ADVISORY REPORT:
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 ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- -
6400 .0625 18334401 .9244 694123290 78848 22784
12800 .125 18334401 .9244 694123290 78848 22784
19200 .1875 18334401 .9244 694123290 78848 22784
25600 .25 18334401 .9244 694123290 78848 22784
32000 .3125 44322680 2.2347 2.1805E+10 7168 20224
38400 .375 34798471 1.7545 2.1805E+10 7168 26624
44800 .4375 28646014 1.4443 1.3125E+10 14336 24576
51200 .5 24847833 1.2528 7767170203 21504 24576
57600 .5625 22975519 1.1584 5125545198 28672 24576
64000 .625 21785489 1.0984 3448265680 35840 24576
70400 .6875 21006019 1.0591 2348774389 43008 24576
76800 .75 20528023 1.035 1674433612 50176 24576
83200 .8125 20226549 1.0198 1248380737 57344 22784
89600 .875 20028211 1.0098 968996113 64512 22784
96000 .9375 19899291 1.0033 786233451 72960 22272
102400 1 19833839 1 694123290 78848 22784 -->> CURRENT SETTING
108800 1.0625 19780288 .9973 619227387 86016 22784
115200 1.125 19738637 .9952 560921031 93184 22528
121600 1.1875 19704919 .9935 513165348 100352 22272
128000 1.25 19683102 .9924 479778018 107520 21760
134400 1.3125 19663268 .9914 452846034 114688 21504
140800 1.375 19647401 .9906 429592904 121856 20992
147200 1.4375 19633517 .9899 409393916 129024 20736
153600 1.5 19625584 .9895 394123204 136192 20480
160000 1.5625 19619634 .9892 394123204 136192 26624
166400 1.625 19609717 .9887 380310151 143360 24576
172800 1.6875 19605750 .9885 380310151 143360 32768
179200 1.75 19601783 .9883 380310151 143360 38912
185600 1.8125 19599800 .9882 380310151 143360 40960
192000 1.875 19599800 .9882 380310151 143360 40960
198400 1.9375 19599800 .9882 380310151 143360 40960
204800 2 19599800 .9882 380310151 150528 40960
SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS ESTD_BUFFER_CACHE_SIZE ESTD_SHARED_POOL_SIZE
---------- --------------- ------------ ------------------- ------------------- ---------------------- --------------------- -
102400 1 19833839 1 694123290 78848 22784 -->> CURRENT SETTING
153600 1.5 19625584 .9895 394123204 136192 20480
Now if we see the estimated stats for size factor 1.5(i.e with sga_size 153600M), then there is an decrease in est_phyiscal_read from 694123290 to 394123204 , But no significat change in est_db_time. So By considering the estimated improvement in physical read, we can take suggest to increases the sga_target from 102400M to 153600M.
