Buffer pool advisory in AWR

By Franck Pachot

.
In Oracle memory advisors: how relevant ? I said that advisors are calculating their recommendations from statistics cumulated since the begining of the instance, even in AWR which is supposed to cover only a short period. Here is a quick test on buffer pool advisory to validate that assumption.

I’m running the following query to compare the ‘physical reads cache’ from DBA_HIST_SYSSTATS and the value from the advisor in DBA_HIST_DB_CACHE:

SQL> column sysstat_value format 999G999G999G999
SQL> column advisor_value format 999G999G999G999
SQL> select snap_id,sysstat_value,advisor_value from
-- physical reads cache
(select snap_id,dbid,instance_number,stat_name,value sysstat_value from dba_hist_sysstat where stat_name like 'physical reads cache')
natural join
--  ACTUAL_PHYSICAL_READS
(select snap_id,dbid,instance_number,'ADVISOR',actual_physical_reads advisor_value from DBA_HIST_DB_CACHE_ADVICE where size_factor=1 and name='DEFAULT')
order by 1 desc,2,3;

Here is the result where I can see that they match for all snapshots I have in history:

   SNAP_ID    SYSSTAT_VALUE    ADVISOR_VALUE
---------- ---------------- ----------------
      3025      708,373,759      731,359,811
      3024      708,364,027      731,350,072
      3023      708,284,582      731,270,631
      3022      708,281,965      731,268,020
      3021      708,280,406      731,266,424
      3020      708,252,249      731,238,240
...
      2133       45,538,775       46,930,580
      2132       45,533,062       46,924,865
      2131       30,030,094       31,423,247
      2130          138,897          138,406
      2129          125,126          124,637
      2128          114,556          114,052
      2127          113,455          112,959
      2126          112,378          111,890
      2125          111,179          110,682
      2124          106,701          106,197
      2123          104,782          104,287
      2122           59,071           58,578
      2121           57,972           57,476
...

I’m not sure about the three columns available in that view: PHYSICAL_READS BASE_PHYSICAL_READS ACTUAL_PHYSICAL_READS so let’s check that the one I used is the one that is displayed in an AWR report. Here is the latest report for snapshots 3024 to 3025:

Here is how I verified my assumtions, on an instance that is running for a long time. When you read at the advisor recommendations, you should know whether the activity since instance startup is relevant or not. And I don’t know how to reset the counters (except with an instance restart).