Oracle SQL 調優核心目標與排序優化實戰

SQL 調優的三大核心目標

Oracle SQL 調優雖博大精深，但 90% 的工作圍繞三大目標展開，掌握這些原則即可解決多數效能問題：

1. 消除不必要的大表全表掃描

全表掃描產生大量 I/O，拖垮整體效能。判斷標準：

• 有序表：查詢回傳 < 40% 列 → 應用索引
• 無序表：查詢回傳 < 7% 列 → 應用索引

常見手段：建立 B-tree、Bitmap 或 Function-based 索引；必要時用 Hint 強制索引；小表全表掃描則建議置入 KEEP 池（Oracle 8+）或 ALTER TABLE ... CACHE（Oracle 7）。

2. 確保最優索引使用

多索引可選時，需人工檢查執行計畫，確保 Oracle 選用最佳索引（含 Bitmap、Function-based 索引）。

3. 確保最優 JOIN 策略

Nested Loop、Hash Join、Sort-Merge Join 各有適用場景，需根據資料量、索引、排序需求選擇最優方案。

排序操作：被忽視的效能殺手

排序發生於 ORDER BY、GROUP BY、CREATE INDEX、合併連結等場景。每個 Session 獲私有排序區域：

• 專用連接 → PGA，大小由 sort_area_size 決定
• 共享伺服器 → Large Pool

記憶體排序 vs 磁碟排序

若排序超出 sort_area_size，溢寫至 TEMP 表空間進行磁碟排序，**速度慢約 14,000 倍**，且消耗暫存空間、緩衝池，引發 free buffer waits 連鎖反應。

關鍵參數調整

實務建議

1. 監控 V$SORT_USAGE、V$SESSION_WAIT 發現磁碟排序。
2. 大量資料操作（匯入、報表）前臨時調大 sort_area_size（Session 層級 ALTER SESSION）。
3. 避免不必要的 ORDER BY、DISTINCT、UNION（改用 UNION ALL）。
4. 利用索引消除排序：ORDER BY 欄位若有索引，Oracle 可直接按索引順序讀取。

結語

SQL 調優無銀彈，但聚焦「消除不必要全表掃描、最佳化索引與 JOIN、控制排序溢寫」三大面向，配合 SORT_AREA_SIZE 精細調校，即能解決絕大多數效能瓶頸。