Oracle SQL 調優核心三大法則與排序、競爭調整深度解析

SQL 調優三大目標（佔 90% 工作量）

1. 消除不必要的大表全表掃描
   • 有序表回傳 <40%、無序表 <7% 列 → 改用索引
   • 手段：建立 B-tree/Bitmap/Function-based 索引、或用 Hint 強制索引
   • 小表全掃描 → 放入 KEEP 池（8i+）或 ALTER TABLE CACHE (7)
2. 確保最佳索引使用：多索引可選時，驗證 Oracle 選擇正確索引（含 Bitmap/Function-based）
3. 確保最佳 Join 方法：Nested Loop / Hash Join / Sort-Merge Join 視資料量與索引而定

排序操作調整

觸發排序場景

• ORDER BY、GROUP BY
• CREATE INDEX
• Join 時索引不足觸發 Merge Sort

排序區域記憶體管理

• 專用連接：PGA 大小由 SORT_AREA_SIZE 決定
• 共用伺服器：Large Pool 分配
• 所有 Session 共用同一大小，無法單獨擴充
• 超出 → TEMP 表空間磁碟排序（慢 14,000 倍）

關鍵參數

• SORT_AREA_SIZE：排序區總大小
• SORT_AREA_RETAINED_SIZE：單次排序保留大小

磁碟排序副作用

• 消耗 TEMP 空間、佔用緩衝池、引發 free buffer waits、擠佔其他任務資料塊

競爭調整：儲存參數與 DML 效能

下文將詳細探討 FREELISTS、PCTFREE、PCTUSED 如何影響 INSERT/UPDATE 效能與空間重用權衡。