Buffer Hit Ratio

Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.

DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).

Physical Reads - The cumulative number of blocks read from disk.

Logical reads are the sum of consistent gets and db block gets.

The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.

Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora

select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat

Buffer Hit Ratio

• Consistent Gets - 從Block Buffer取得之前查詢過的資料
• DB Block Gets - 藉由single block取得的blocks(不必經由一致性計算機制)
• Physical Reads - 從Disk取得blocks的累計數字
• Logical reads are the sum of consistent gets and db block gets.
• The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
• Hit Ratio 應該大於80%, 否則要增加DB_BLOCK_BUFFERS(init.ora)