Finding Cache Hit Ratios History

Many DBAs consider values of cache hit ratios as mythical, but I personally don’t think so as these have guided me to the right direction many times. But this is really true that by looking at cache hit ratios solely and making conclusions might really lead you to the wrong direction. These values can only be seen and analysed in some context.
High buffer cache hit ratio does not necessarily mean that your database is doing right, and low buffer cache hit ratio does not necessarily mean that your database is doing wrong.

You can check historical values and trends of your cache hit ratios using following combination of queries in 10g and above databases.
Please note that DBA_HIST% view require Oracle Diagnostic Pack license.

Please note that this information is coming from AWR repository and total number of rows returned depends on your AWR Snapshot Interval and AWR Snapshot Retention settings. For this example, I used a database where I have AWR snapshot interval of 30 minutes and AWR snapshot retention of 30 days (I did not paste here all information of 30 days though)

Find METRIC_ID for the metrics you want to check for historical trends

SQL> select metric_id,METRIC_NAME from DBA_HIST_METRIC_NAME where upper(metric_name) like '%CACHE%' order by 1;  METRIC_ID METRIC_NAME ---------- ----------------------------------------------------------------       2000 Buffer Cache Hit Ratio       2000 Buffer Cache Hit Ratio       2050 Cursor Cache Hit Ratio       2098 Global Cache Average CR Get Time       2099 Global Cache Average Current Get Time       2101 Global Cache Blocks Corrupted       2102 Global Cache Blocks Lost       2110 Row Cache Hit Ratio       2111 Row Cache Miss Ratio       2112 Library Cache Hit Ratio       2112 Library Cache Hit Ratio       2113 Library Cache Miss Ratio       2115 PGA Cache Hit %

Find the historical trend of metric. For RAC, also include column INSTANCE_NUMBER in the query

SQL> set lines 200 SQL>  alter session set nls_date_format='DD-MON-Yy HH24:MI:SS'; select BEGIN_TIME,END_TIME,INTSIZE,MINVAL,MAXVAL,AVERAGE from DBA_HIST_SYSMETRIC_SUMMARY where metric_id=2000 order by begin_time; BEGIN_TIME         END_TIME              INTSIZE     MINVAL     MAXVAL    AVERAGE ------------------ ------------------ ---------- ---------- ---------- ---------- 21-MAY-15 00:21:48 21-MAY-15 00:51:48     179998          0 99.9229614   98.89138 21-MAY-15 00:51:48 21-MAY-15 01:21:47     179910          0 99.4784251 96.9950951 21-MAY-15 01:21:47 21-MAY-15 01:51:47     180052          0 97.9948758 95.0564343 21-MAY-15 01:51:47 21-MAY-15 02:21:47     180007          0 99.8254444 95.9288337 21-MAY-15 02:21:47 21-MAY-15 02:51:47     179986          0 99.8268827 98.3058298 21-MAY-15 02:51:47 21-MAY-15 03:21:47     179989          0 99.8828385 98.3934493 21-MAY-15 03:21:47 21-MAY-15 03:51:47     179995          0 99.5284186 98.0050365 21-MAY-15 03:51:47 21-MAY-15 04:21:47     180004          0 99.6234565  97.367626 21-MAY-15 04:21:47 21-MAY-15 04:51:47     180016          0 99.3352565 96.9461415 21-MAY-15 04:51:47 21-MAY-15 05:21:47     180017          0 98.8389462 95.5678732 21-MAY-15 05:21:47 21-MAY-15 05:51:47     179980          0 99.7772791 98.0900195 BEGIN_TIME         END_TIME              INTSIZE     MINVAL     MAXVAL    AVERAGE ------------------ ------------------ ---------- ---------- ---------- ---------- 21-MAY-15 05:51:47 21-MAY-15 06:21:47     179991          0 99.8044275 97.7869703 21-MAY-15 06:21:47 21-MAY-15 06:51:47     180000          0 99.6123821 97.5314016 21-MAY-15 06:51:47 21-MAY-15 07:21:47     179988          0 99.7801794 98.2470852 21-MAY-15 07:21:47 21-MAY-15 07:51:47     180004          0 99.7263433 97.7974523 21-MAY-15 07:51:47 21-MAY-15 08:21:47     179988          0 99.7177159 97.2940065 21-MAY-15 08:21:47 21-MAY-15 08:51:47     179997          0 99.9238153 98.4526479 21-MAY-15 08:51:47 21-MAY-15 09:21:47     180002          0 99.9203255 98.7130192 21-MAY-15 09:21:47 21-MAY-15 09:51:47     180004          0 99.7603138 95.6753558

BEGIN_TIME column shows begin time of snapshot interval

END_TIME column shows the end time of snapshot interval
INITSIZE shows interval duration which is 180000 centi-seconds or 30 minutes in this case

MINVAL shows minimum value recorded for the metrics during the interval

MAXVAL shows maximum value recorded for the metrics during the interval

AVERAGE shows average value for the metrics during the interval