select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') currenttime,round(1-(physical_read-physical_reads_direct)/(db_block_gets+consistent_gets-physical_reads_direct),4)*100 "Buffer Cache Hit Ratio",
       buffer_busy_waits,free_buffer_waits
from (SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd hh24:mi:ss') Time, s.*, e.*
FROM (SELECT SUM(decode(name, 'physical reads', VALUE)) physical_read,
               NVL(SUM(decode(name, 'physical reads direct', VALUE)), 0) physical_reads_direct,
               SUM(decode(name, 'db block gets', VALUE)) db_block_gets,
               SUM(decode(name, 'consistent gets', VALUE)) consistent_gets
          FROM v$sysstat
         WHERE name IN ('physical reads', 'db block gets', 'consistent gets', 'physical reads direct')) s,
       (SELECT NVL(SUM(decode(e.event, 'buffer busy waits', e.total_waits)), 0) buffer_busy_waits,
       NVL(SUM(decode(e.event, 'free buffer waits', e.total_waits)), 0) free_buffer_waits
          FROM v$system_event e
         WHERE e.event IN ('buffer busy waits', 'free buffer waits')) e)

ITPub博客

[Oracle Script] Buffer Cache Hit Ratio

最新文章