Oracle 專家調優祕笈：外部環境、Row Resquencing、SQL 調優、排序、競爭五大核心篇

前言

Oracle 調優參數繁多，但核心目標單一：減少 I/O。本文精選五大必修課題。

一、外部環境調優（先於資料庫調優）

• CPU：運行佇列 > CPU 核心數 → CPU 瓶頸。對策：增 CPU 或關閉 Parallel Query
• 記憶體：發生 Paging → 實體記憶體不足。對策：加記憶體、縮減 SGA、關閉 MTS
• 網路：大量 Net8 通訊拖慢 SQL

工具：vmstat, glance, top, sar 監控佇列與分頁。DBA 首要責任：確保 OS 資源充足，否則資料庫調優無效。

二、Row Resquencing（列重排序）極致減 I/O

• 原理：將表資料按主鍵/索引鍵順序物理重組（CTAS 或 9i 重組工具），使範圍掃描時連續區塊對應連續鍵值
• 效果：索引範圍掃描 100 列，未重組需 100 次區塊讀取；重組後僅 3 次（1 索引 + 2 資料區塊）
• 判斷依據：DBA_INDEXES.CLUSTERING_FACTOR 接近區塊數 → 順序佳；接近列數 → 順序差
• 適用：OLTP 大量範圍查詢；隨機單鍵存取無效益

三、SQL 調優四大鐵律

1. 消除不必要全表掃描：有序表回傳 <40%、無序表 <7% 列 → 改用索引。手段：加索引（B-Tree/Bitmap/Function-Based）或 Hint 強制
2. 小表全表掃描入緩存：ALTER TABLE ... CACHE (7) 或放入 KEEP 池 (8i+)
3. 確保最優索引使用：多索引可選時，檢查執行計劃強制正確索引
4. 確保最優 JOIN 方法：Nested Loop / Hash Join / Sort-Merge 依資料量與索引選擇

這四條佔 SQL 調優 90% 工作量，無需深究內部機制即可見效。

四、調優排序操作

• 觸發排序：ORDER BY、GROUP BY、CREATE INDEX、索引不足導致 Merge Sort Join
• 記憶體排序區：專用連接用 SORT_AREA_SIZE (PGA)；MTS 用 LARGE_POOL
• 磁碟排序比記憶體排序慢 ~14,000 倍，消耗 TEMP 表空間與緩衝區
• 關鍵參數：SORT_AREA_SIZE、SORT_AREA_RETAINED_SIZE
• 目標：避免磁碟排序，平衡記憶體分配與浪費

五、調優競爭（FREELIST 與儲存參數）

• PCTFREE 控制區塊從 Freelist 移出閾值（預留擴展空間）
• PCTUSED 控制區塊重回 Freelist 閾值
• 高 PCTUSED → 空間重用好但 I/O 多（區塊滿得快）
• 低 PCTUSED → 效能佳（區塊能裝更多列再滿），擴展新區塊比回收舊區塊快
• 鐵律：PCTUSED 至少能容納一列，否則產生「死區塊」拖慢 INSERT
• CHAINED ROWS → PCTFREE 過低或 DB_BLOCK_SIZE 過小
• FREELISTS = 最大並發 INSERT 數；FREELIST GROUPS 僅用於 RAC（= 實例數）