--物理讀寫操作
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,
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
/*
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';