Buffer hit ratio in every hour

In response to providing evidence for my suspection that the db_cache_size is low on a 10g production database, I wrote a script to display buffer hit ratio in every hour.

rem  buffer hit ratio on hourly basis for the past day
rem   -- 10 g
rem 
select AA.start_hour, 
       round(( 1-CC.phys_read/
       (AA.consistent_get + BB.db_blk_get))*100,1) buffer_hit_ratio
from (
 select 
  to_char(s.begin_interval_time, 'YYYY-MM-DD HH24') start_hour, 
  sum(e.value - b.value) consistent_get
  from
       dba_hist_sysstat b,
       dba_hist_sysstat e,
       dba_hist_snapshot s
  where b.snap_id = s.snap_id
   and  b.snap_id = e.snap_id -1
   and b.stat_id = 4162191256
   and b.stat_id = e.stat_id
   and s.begin_interval_time >= trunc(sysdate)-1 
   and s.begin_interval_time < trunc(sysdate) 
 group by  to_char(s.begin_interval_time, 'YYYY-MM-DD HH24')
 )  AA,
 (
 select to_char(s.begin_interval_time, 'YYYY-MM-DD HH24') start_hour, 
        sum(e.value - b.value) db_blk_get
  from
       dba_hist_sysstat b,
       dba_hist_sysstat e,
       dba_hist_snapshot s
  where b.snap_id = s.snap_id
   and  b.snap_id = e.snap_id -1
   and b.stat_id =1480709069 
   and b.stat_id = e.stat_id
   and s.begin_interval_time >= trunc(sysdate)-1 
   and s.begin_interval_time < trunc(sysdate) 
 group by to_char(s.begin_interval_time, 'YYYY-MM-DD HH24')
 ) BB,
 (
 select to_char(s.begin_interval_time, 'YYYY-MM-DD HH24') start_hour, 
        sum(e.value - b.value) phys_read 
  from
       dba_hist_sysstat b,
       dba_hist_sysstat e,
       dba_hist_snapshot s
  where b.snap_id = s.snap_id
   and  b.snap_id = e.snap_id -1
   and b.stat_id = 2263124246 
   and b.stat_id = e.stat_id
   and s.begin_interval_time >= trunc(sysdate)-1 
   and s.begin_interval_time < trunc(sysdate) 
 group by to_char(s.begin_interval_time, 'YYYY-MM-DD HH24')
 ) CC
where AA.start_hour=BB.start_hour
and   BB.start_hour=CC.start_hour
order by 1
/


The output for the database looks like:


system@THEDB> @buffer_hit_hour.sql

START_HOUR    BUFFER_HIT_RATIO
------------- ----------------
2009-04-22 00             46.2
2009-04-22 01             42.4
2009-04-22 02             28.9
2009-04-22 03             35.6
2009-04-22 04             34.4
2009-04-22 05             37.9
2009-04-22 06             47.3
2009-04-22 07             58.1
2009-04-22 08             68.7
2009-04-22 09             86.9
2009-04-22 10             62.2
2009-04-22 11             58.3
2009-04-22 12             68.6
2009-04-22 13             67.3
2009-04-22 14             70.9
2009-04-22 15             79.7
2009-04-22 16             66.9
2009-04-22 17             49.5
2009-04-22 18               44
2009-04-22 19             54.7
2009-04-22 20             22.8
2009-04-22 21             55.6
2009-04-22 22             53.7
2009-04-22 23             53.5