Oracle 查詢優化四大核心策略（上）：提升命中率

前言

資料庫效能直接決定應用系統回應速度。隨著資料量成長，查詢效率下降尤為明顯。本文結合實務經驗，從命中率提升、多表查詢優化、大表查詢優化、SQL 優化四大面向闡述 Oracle 查詢優化策略。

一、命中率（Hit Ratio）影響因素與對策

命中率 = 從記憶體取得資料的比率。影響因素四大類：

1. 資料字典表活動

• SQL 首次執行需解析，讀取字典資訊至 SGA。
• 字典表較小，可透過 ALTER TABLE dict_table CACHE 置於 Keep Pool，提升字典存取速度。
• 但過度佔用 SGA 會擠佔用戶資料空間，需權衡。

2. 暫存段活動

• 排序區 (SORT_AREA_SIZE) 不足時會寫入暫存段，降低 OLTP 命中率。
• 查詢排序統計：

SELECT * FROM v$sysstat WHERE name IN ('sorts (disk)','sorts (memory)');

• 若磁碟排序佔比高，調大 SORT_AREA_SIZE (如 65536 → 131072)，修改 init.ora 後重啟生效。

3. 回滾段活動

• 回滾段標頭區塊存取頻繁，嚴重影響 OLTP 命中率。
• 監控「資料區塊一致性讀 - 重做記錄應用」統計值，判斷是否因回滾段導致命中率下降。

4. 全表掃描

• 全掃區塊在緩存停留時間極短，拉低命中率。
• 對策：

1. 建立合適索引，避免不必要全表掃描。
2. 高頻存取小表置入 Keep Pool：ALTER TABLE your_table CACHE 或指定 STORAGE (BUFFER_POOL KEEP)。

小結

命中率優化核心在於「減少物理讀、增加記憶體命中」。下篇將深入多表聯結、大表分頁與 SQL 寫法優化。