Oracle 效能調優實戰指南：從外部環境到 SQL 與儲存參數的全方位優化

一、調優順序原則：先外部、後內部

許多 DBA 錯誤地直接調參數，卻忽略伺服器資源瓶頸。正確順序：

1. 外部資源檢查：CPU、記憶體、網路、磁碟 I/O。
2. 資料庫參數與結構調整：SGA、PGA、排序區、Undo、Redo。
3. SQL 與應用層優化：執行計畫、索引、統計資訊、綁定變數。

若 CPU 排隊長度超過核心數、或記憶體發生 paging/swapping，任何 SQL 調優效果皆有限。

二、外部環境瓶頸快速診斷

三、Row Resquencing（列重排序）：大幅降低磁碟 I/O 的秘技

原理

當查詢透過索引範圍掃描讀取大量連續鍵值時，若表資料物理順序與索引順序一致（CLUSTERING_FACTOR 接近區塊數），單次多區塊讀取即可滿足數百筆資料，避免大量單區塊隨機 I/O。

效益試算

-- 未重排：100 筆資料分散在 100 個區塊 → 100 次單區塊讀取
-- 重排後：100 筆資料集中在 2-3 個區塊 → 1 次索引讀取 + 2-3 次多區塊讀取
-- I/O 減少 97% 以上

實作方式

1. 檢視 DBA_INDEXES.CLUSTERING_FACTOR 與 DBA_TABLES.BLOCKS 差距。
2. 使用 CREATE TABLE new_t AS SELECT * FROM old_t ORDER BY pk_col;（CTAS）重建表。
3. 11g+ 可用 DBMS_REDEFINITION 聯機重組，業務零停機。

四、SQL 調優四大黃金法則

1. 消除不必要的全表掃描：回傳行數 < 40%（有序表）或 < 7%（無序表）時，應走索引。手段：建立 B-tree / Bitmap / Function-based 索引，或加 Hint。
2. 小表全掃描放入 KEEP 池：ALTER TABLE small_t CACHE;（7）或 ALTER TABLE small_t STORAGE (BUFFER_POOL KEEP);（8+）。
3. 確保最佳索引與 Join 方法：比較 Nested Loop / Hash Join / Sort-Merge Join 成本，必要時用 LEADING、USE_NL、USE_HASH 強制指定。
4. 善用綁定變數：減少 Hard Parse，提升 Cursor Sharing，避免 Shared Pool 碎片化。

五、排序調優：記憶體 vs 磁碟的 14,000 倍差距

• SORT_AREA_SIZE（專用連線）或 PGA_AGGREGATE_TARGET（自動 PGA 管理）決定記憶體排序上限。
• 超過上限 → 寫入 TEMP 表空間進行磁碟排序，效能暴跌。
• 監控：V$SORT_USAGE、V$SQL_WORKAREA_ACTIVE 觀察 ONEPASS / MULTIPASS 比例。
• 對策：調大 PGA、避免不必要的 ORDER BY/GROUP BY、建立索引消除排序。

六、儲存參數與 Freelist：高併發 DML 的關鍵

核心參數

• PCTFREE：預留空間給 UPDATE 增長，避免 Row Migration/Chaining。
• PCTUSED：區塊使用率降至此值才回到 Freelist，供 INSERT 重用。
• FREELISTS：設為最大併發 INSERT 會話數，減少 Freelist 爭用。
• FREELIST GROUPS：僅 RAC/Parallel Server 有效，設為實例數。

設定原則

1. 確保 Freelist 上的區塊至少能容納一筆記錄，否則會讀取 5 個「死區塊」才延伸新區塊，嚴重拖慢 INSERT。
2. 高 PCTUSED → 空間重用率高、但 I/O 多；低 PCTUSED → 高效能、空間浪費大。依業務取捨。
3. 出現 Chained Row 檢查 PCTFREE 是否過低或 DB_BLOCK_SIZE 過小。

七、總結：建立持續調優的閉環流程

1. 收集基線統計（AWR/Statspack、OS 指標）。
2. 定義明確目標（如：核心報表 < 3 秒、批次作業縮短 30%）。
3. 依優先級依序實施：外部資源 → 實例參數 → 結構重組 → SQL 改寫。
4. 驗證效果、記錄變更、更新基線。
5. 定期重複（建議每季或重大變更後）。

調優沒有銀彈，只有「測量 → 分析 → 優化 → 驗證」的科學迭代，才能讓 Oracle 持續發揮最佳效能。