Oracle memory advisors: how relevant ?

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 ?