Oracle 效能調優核心指標體系：12 大命中率計算、等待事件分析與 SQL 優化實戰方法論

命中率指標體系：計算公式、健康閾值與調優動作

六大 Buffer Cache 低命中率診斷步驟

1. 確認命中率： `SELECT ROUND((1-(phy_reads/(db_blk_gets+con_gets)))*100,2) FROM V$SYSSTAT;` < 90% 才算低。
2. 檢查 Free Buffer： `SELECT COUNT(*), status FROM V$BH GROUP BY status;` 無 FREE 不一定要加記憶體，結合當前大小判斷。
3. 等待事件分析： `SELECT event, total_waits FROM V$SYSTEM_EVENT WHERE EVENT='free buffer waits';` 有等待 → DBWn 寫入跟不上或緩衝區過小/過大。
4. 當前大小： `SELECT value/1024/1024 FROM V$PARAMETER WHERE NAME='db_cache_size';` 評估是否已達硬體上限。
5. Top 等待事件： `db file scattered read` 佔比高 → **大量全表掃描**，優化 SQL/建索引優於加記憶體。
6. 參考建議： `SELECT * FROM V$DB_CACHE_ADVICE;` 9i+ 新特性，模擬不同大小下的實體讀預估。

高耗 SQL 定位三板斧

1. SQL Trace + TKPROF： `ALTER SESSION SET SQL_TRACE=TRUE;` → 執行 → `tkprof` 分析磁碟讀/邏輯讀/耗時 Top SQL。
2. Statspack/AWR： 繁忙時段快照 → Top 事件含 `db file scattered read` → Top SQL 分析執行計畫、索引缺失、統計過期。
3. V$SQLAREA 即時查詢：
   • 排序最多：`SELECT * FROM V$SQLAREA ORDER BY SORTS DESC;`
   • 磁碟讀最多：`SELECT * FROM (SELECT sql_text, disk_reads/executions FROM V$SQL WHERE executions>0 ORDER BY 2 DESC) WHERE ROWNUM<11;`
   • 低效 SQL（命中率低）：`SELECT * FROM V$SQLAREA WHERE executions>0 AND buffer_gets>0 AND (buffer_gets-disk_reads)/buffer_gets<0.8 ORDER BY 4;`

硬體與參數調整的邊界認知

• 硬體允許下**適度增大 DB_CACHE_SIZE** 減少實體讀，**但非越大越好**（管理開銷、檢查點耗時、換頁風險）。
• **物盡其用、精準定位瓶頸**才是藝術：Buffer Cache 低 → 先看 Top SQL 有無全表掃描 → 有則優化 SQL/加索引 → 無則評估加記憶體/KEEP 池隔離熱表。
• 索引是在**不修改程式碼**情況下提升效能**最實用**手段。

常用診斷 SQL 快速參考卡

-- 當前會話正在執行的 SQL (需 SID)
SELECT * FROM V$SQLAREA WHERE ADDRESS=(SELECT SQL_ADDRESS FROM V$SESSION WHERE SID=&SID);

-- 緩衝區命中率快速查
SELECT ROUND((1-(phy/(db_blk+con)))*100,2)||'%' FROM 
(SELECT VALUE phy FROM V$SYSSTAT WHERE NAME='physical reads'),
(SELECT VALUE db_blk FROM V$SYSSTAT WHERE NAME='db block gets'),
(SELECT VALUE con FROM V$SYSSTAT WHERE NAME='consistent gets');

-- 共享池命中率快速查
SELECT SUM(pinhits)/SUM(pins)*100||'%' FROM V$LIBRARYCACHE;

-- 排序記憶體/磁碟比
SELECT name, value FROM V$SYSSTAT WHERE NAME LIKE '%sort%';