This was first published on https://blog.dbi-services.com/oracle-memory-advisors-how-relevant (2015-06-12)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
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.
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.
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.
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 ?
In an 11.2.0.4 AWR, Instance Activity Stats shows Physical Reads 86million and the Buffer Pool Advisory shows 45million. In 11.2.0.3 I see the Buffer Pool Advisory showing a much higher count than the Instance Activity Stats.
Hi Hemant, This post is just coming from one quick test, so maybe I made the conclusion too quickly. I’ll check that in depth from DBA_HIST. For Statspack it’s easy we can read the code the we know where the figures comes from. thanks, Franck.