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 significate 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.
Recommended Posts
