By Franck Pachot
.
Do you look at memory advisors? I usually don’t but I still show them during the performance tuning workshop because there is no easy way to get an idea about memory sizing. This morning, while giving the training, I’ve shown on our workshop environment what the problem is: they are based on statistics cumulated from instance startup, which can cover months of heterogeneous activity, and then the result is probably meaningless.
If you want to trust advisors, then you need to know on which measures it is based. Let’s check it.
V$DB_CACHE_ADVISOR
Here is how we query the V$DB_CACHE_ADVISOR in order to see the estimated physical reads for different buffer cache size:
SQL>
SELECT a.size_for_estimate "Buffer size MB",
a.size_factor "Factor size",
round(a.estd_physical_read_time/1000,2) "Estim. time (s)",
a.estd_physical_read_factor "Estim. time factor",
a.estd_physical_reads "Estim. nb physical read"
FROM sys.v$db_cache_advice a
WHERE a.name='DEFAULT'
ORDER BY a.size_for_estimate;
Buffer size MB Factor size Estim. time (s) Estim. time factor Estim. physical read
-------------- ----------- --------------- ------------------ --------------------
24 .0909 1.31 8.4871 23424349
48 .1818 1.16 7.5612 20868825
72 .2727 .37 2.3838 6579289
96 .3636 .31 1.9787 5461235
120 .4545 .26 1.6831 4645325
144 .5455 .23 1.4912 4115679
168 .6364 .21 1.3713 3784848
192 .7273 .2 1.2564 3467715
216 .8182 .18 1.1418 3151277
240 .9091 .16 1.0568 2916629
264 1 .16 1 2759998
288 1.0909 .15 .9351 2580935
312 1.1818 .14 .8736 2411003
336 1.2727 .13 .8291 2288418
360 1.3636 .12 .7918 2185486
384 1.4545 .12 .7537 2080272
408 1.5455 .11 .7035 1941706
432 1.6364 .1 .6479 1788252
456 1.7273 .09 .6021 1661696
480 1.8182 .09 .554 1529086
Look at the factor 1 – the current values. The advisor is based on 2.7 million physical reads. Let’s see if it is based on statistics since instance startup or a shorter period.
V$SYSTAT
I’ll display the instance statistics (cumulative since instance startup) that measure physical reads:
SQL> select value,name from v$sysstat where name like 'physical reads %';
VALUE NAME
---------- ----------------------------------------------------------------
2760403 physical reads cache
86342292 physical reads direct
33656 physical reads direct temporary tablespace
76909 physical reads cache prefetch
13105 physical reads prefetch warmup
0 physical reads retry corrupt
3428 physical reads direct (lob)
0 physical reads for flashback new
0 physical reads cache for securefile flashback block new
0 physical reads direct for securefile flashback block new
Here it’s clear: the advisor was based on the 2.7 million physical reads to cache. Those values are cumulated from instance startup. If the instance have been started a long time ago then there is nothing relevant here: activity is not regular, memory component have been resized several times, etc. And if the instance has been started recently, then the cache activity is not significant: you did lot of physical reads to load the cache.
AWR / Statspack
Ok. we know that V$ views are cumulative from instance start. When we want to look at statistics on a shorter period of time we have AWR or Statspack. Here is the Buffer Cache advisor section:
Buffer Pool Advisory DB/Inst: DB1/DB1 Snap: 61
-> Only rows with estimated physical reads >0 are displayed
-> ordered by Block Size, Buffers For Estimate
Est
Phys Estimated Est
Size for Size Buffers Read Phys Reads Est Phys % DBtime
P Est (M) Factor (thousands) Factor (thousands) Read Time for Rds
--- -------- ------ ------------ ------ -------------- ------------ -------
D 24 .1 3 8.5 23,422 1 1307.0
D 48 .2 6 7.6 20,866 1 1164.0
D 72 .3 9 2.4 6,579 1 368.0
D 96 .4 12 2.0 5,461 1 306.0
D 120 .5 15 1.7 4,645 1 260.0
D 144 .5 18 1.5 4,116 1 231.0
D 168 .6 21 1.4 3,785 1 213.0
D 192 .7 24 1.3 3,468 1 195.0
D 216 .8 26 1.1 3,151 1 177.0
D 240 .9 29 1.1 2,917 1 164.0
D 264 1.0 32 1.0 2,760 1 155.0
D 288 1.1 35 0.9 2,581 1 145.0
D 312 1.2 38 0.9 2,411 1 136.0
D 336 1.3 41 0.8 2,289 1 129.0
D 360 1.4 44 0.8 2,186 1 123.0
D 384 1.5 47 0.8 2,080 1 118.0
D 408 1.5 50 0.7 1,942 1 110.0
D 432 1.6 53 0.6 1,788 1 101.0
D 456 1.7 56 0.6 1,662 1 94.0
D 480 1.8 59 0.6 1,529 1 87.0
------------------------------------------------------
this looks like the cumulative values from instance startup. But I want to be sure – not guess. The instance statistics section can show get the number of physical reads to cache during this period of time:
Instance Activity Stats DB/Inst: DB1/DB1 Snaps: 60-61
-> Ordered by statistic name
Statistic Total per Second per Trans
-------------------------------- ------------------ -------------- -------------
physical reads 1,973 22.9 140.9
physical reads cache 1,863 21.7 133.1
only few thousand of them here. This confirms that the advisor is not based on delta values.
Conclusion
From what we see, the memory advisors are based on values cumulated since instance startup. I always advise to focus the performance analysis on a short period where activity is regular. Then I can’t advise to use those advisors. You can look at it in the few following days after instance startup, just to have an idea, but don’t rely only on that. In my opinion, that a bug. there is no reason to show cumulative values in a Statspack / AWR report. I think that the advisor can do similar estimations on delta values. Anyone volunteer to open a bug ?