Wednesday, May 20, 2015

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

Search Oracle Community Forums