Oracle緩衝區命中率精確計算與共享池、排序、Redo指標綜合監控

從統計視圖到健康儀表板：核心命中率指標一站式查詢指南

整合V$SYSSTAT統計值，提供緩衝區、共享池、排序、Redo Log Buffer四大維度即時監控SQL與判讀標準。

一、緩衝區命中率(兩種計算法)

方法A：經典公式(含Direct Path干擾)

SELECT a.value+b.value "Logical Reads", c.value "Physical Reads",
       ROUND(100*((a.value+b.value)-c.value)/(a.value+b.value)) "Buffer Hit Ratio"
FROM v$sysstat a, v$sysstat b, v$sysstat c
WHERE a.statistic#=40 AND b.statistic#=41 AND c.statistic#=42;
-- statistic#: 40=db block gets, 41=consistent gets, 42=physical reads

方法B：官方精確版(排除Direct Path/Lob)

-- 取得四個關鍵統計值
SELECT name, value FROM v$sysstat
WHERE name IN ('physical reads','physical reads direct',
               'physical reads direct (lob)','consistent gets','db block gets');

-- 計算邏輯
X = physical reads direct + physical reads direct (lob)
Hit Ratio = 100 - (physical reads - X) / (consistent gets + db block gets - X) * 100

-- 判讀：< 90% 需關注，考慮增大DB_CACHE_SIZE或優化SQL

二、共享池命中率

SELECT SUM(pinhits)/SUM(pins)*100 "Hit Ratio" FROM v$librarycache;
-- < 95% 需檢查：未使用綁定變數、字面量SQL泛濫、Shared Pool過小

三、排序效能指標

SELECT name, value FROM v$sysstat WHERE name LIKE '%sort%';
-- 關鍵比率：sorts(disk) / (sorts(memory) + sorts(disk))
-- 比率過高 → SORT_AREA_SIZE/PGA_AGGREGATE_TARGET 偏小

四、Redo Log Buffer指標

SELECT name, value FROM v$sysstat
WHERE name IN ('redo entries','redo buffer allocation retries');
-- 關鍵比率：redo buffer allocation retries / redo entries
-- > 1% 考慮增大 LOG_BUFFER

五、整合監控腳本範例(Shell+SQL)

#!/bin/bash
# daily_health_check.sh
sqlplus -s / as sysdba <