statspack的IO操作及Buffer Hit Rate

--物理讀寫操作
select distinct to_char(snap_time, 'yyyy-mm-dd HH24:MI:SS') datetime,
                (newreads.value - oldreads.value) reads,
                (newwrites.value - oldwrites.value) writes
  from perfstat.stats$sysstat  oldreads,
       perfstat.stats$sysstat  newreads,
       perfstat.stats$sysstat  oldwrites,
       perfstat.stats$sysstat  newwrites,
       perfstat.stats$snapshot sn
 where newreads.snap_id = sn.snap_id
   and newwrites.snap_id = sn.snap_id
   and oldreads.snap_id = sn.snap_id - 1
   and oldwrites.snap_id = sn.snap_id - 1
   and oldreads.statistic# = 42  --42 physical reads
   and newreads.statistic# = 42
   and oldwrites.statistic# = 46  --46 physical writes
   and newwrites.statistic# = 46
   and (newreads.value - oldreads.value) > 0
   and (newwrites.value - oldwrites.value) > 0
   and snap_time > to_date('2008-07-08 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
;

--Buffer命中率
select substr(to_char(snap_time, 'yyyy-mm-dd HH24:MI'), 12),
       round(100 * (((a.value - e.value) + (b.value - f.value)) -
             (c.value - g.value)) /
             ((a.value - e.value) + (b.value - f.value))) "BUFFER HIT RATIO"
  from perfstat.stats$sysstat  a,
       perfstat.stats$sysstat  b,
       perfstat.stats$sysstat  c,
       perfstat.stats$sysstat  d,
       perfstat.stats$sysstat  e,
       perfstat.stats$sysstat  f,
       perfstat.stats$sysstat  g,
       perfstat.stats$snapshot sn
 where a.snap_id = sn.snap_id
   and b.snap_id = sn.snap_id
   and c.snap_id = sn.snap_id
   and d.snap_id = sn.snap_id
   and e.snap_id = sn.snap_id - 1
   and f.snap_id = sn.snap_id - 1
   and g.snap_id = sn.snap_id - 1
   and a.statistic# = 40
   and e.statistic# = 40
   and b.statistic# = 41
   and f.statistic# = 41
   and c.statistic# = 42
   and g.statistic# = 42
   and d.statistic# = 46
   and snap_time > to_date('2008-07-08 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
;
/*
40 db block gets 
41 consistent gets 
42 physical reads 
46 physical writes
*/
--buffer 命中率計算公式
公式一：
select name,
       ((consistent_gets + db_block_gets) - physical_reads) /
       (consistent_gets + db_block_gets) * 100 "Hit Ratio%"
  from v$buffer_pool_statistics
 where physical_reads > 0;?
公式二：
Hit Ratio = 1 - ((physical reads - physical reads direct - physical reads direct (lob)) /
(db block gets + consistent gets - physical reads direct - physical reads direct (lob))
公式三：
hit ratio = 1 - [physical reads/(block gets + consistent gets)]

SELECT NAME,
       PHYSICAL_READS,
       DB_BLOCK_GETS,
       CONSISTENT_GETS,
       1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)) "Hit Ratio"
  FROM V$BUFFER_POOL_STATISTICS
 WHERE NAME = 'DEFAULT';