This was first published on https://blog.dbi-services.com/buffer-pool-advisory-in-awr (2015-06-23)
Republishing here for new followers. The content is related to the the versions available at the publication date
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).