Oracle 效能調優入門與進階：從外部環境到 SQL、排序、競爭全維度解析

Oracle 調優是系統性工程，涵蓋作業系統層面、I/O 架構、SQL 執行計畫、排序記憶體、儲存參數等多個維度。本文依循「由外而內、由粗到細」的邏輯，系統梳理核心調優知識點與實戰策略。

一、外部環境調優：調優前的必要前提

Oracle 不在真空中運行。若 CPU、記憶體、網路出現瓶頸，任何資料庫層面的調整皆事倍功半。

工具：vmstat、sar、top、glance (HP-UX) 監控 OS 層面指標。

二、Row Resequencing (列重排序) — 降低磁碟 I/O 核心技巧

核心原則：磁碟 I/O 比記憶體慢 10,000 倍。若應用常以索引範圍掃描讀取連續鍵值，將表資料按主鍵/索引鍵順序物理重排，可將隨機 I/O 轉為循序 I/O，大幅降低區塊讀取次數。

適用場景

• 大表、高容量 OLTP。
• 查詢常用 WHERE col LIKE 'prefix%' 或範圍條件，回傳多筆連續鍵值。
• 不適用：隨機單筆主鍵查詢。

實測效果

同樣查詢 100 筆資料：

• 未重排：需 100 次區塊讀取 (索引 + 100 個分散資料塊)。
• 重排後：僅 3 次讀取 (1 次索引 + 2 個連續資料塊)，I/O 減少 97%。

判斷依據

查詢 DBA_INDEXES.CLUSTERING_FACTOR：

• 接近 BLOCKS (表區塊數) → 資料與索引順序一致，無需重排。
• 接近 NUM_ROWS (表列數) → 順序嚴重不一致，重排效益極大。

實作工具

• CREATE TABLE ... AS SELECT ... ORDER BY pk_col (CTAS)
• Oracle 9i+ 內建 DBMS_REDEFINITION 線上重組。

三、SQL 調優四大黃金法則

1. 消除不必要的大表全表掃描
   有序表回傳 < 40%、無序表回傳 < 7% 時應走索引。手段：建立 B-tree/Bitmap/Function-based 索引、使用 INDEX Hint 強制走索引。
2. 小表全掃描快取到 KEEP Pool
   Oracle 7 用 ALTER TABLE ... CACHE；8i+ 強制置入 KEEP Buffer Pool，避免被淘汰。
3. 確保最優索引使用
   多索引可選時，檢查執行計畫確認 Oracle 選擇正確索引。
4. 確保最優 Join 方法
   Nested Loop / Hash Join / Sort-Merge Join 視資料量、索引、排序成本動態選擇。

這四條規則涵蓋 90% SQL 調優場景，無需深究內部機制即可見效。

四、排序調優：避免磁碟排序

• 排序觸發：ORDER BY、GROUP BY、CREATE INDEX、索引不足導致 Merge Sort Join。
• 記憶體排序區：專用連接用 SORT_AREA_SIZE (PGA)；共享伺服器用 LARGE_POOL。
• 磁碟排序成本：比記憶體排序慢 ~14,000 倍，佔用 TEMP 表空間、增加 Buffer Busy Waits。
• 關鍵參數：SORT_AREA_SIZE、SORT_AREA_RETAINED_SIZE。10g+ 建議啟用 PGA_AGGREGATE_TARGET 自動管理。

五、儲存參數與競爭調優：FREELIST、PCTFREE、PCTUSED

高 DML (INSERT/UPDATE/DELETE) 系統的效能關鍵在於區塊重用效率。

實戰設定原則

1. PCTUSED 設為能容納「至少一列」的最低值，避免死區塊。
2. 出現 Chained Rows → 檢查 PCTFREE 過低或 DB_BLOCK_SIZE 過小 (LONG/RAW 大欄位除外)。
3. 表若有大量並發 INSERT，需對應並發 DELETE，否則單一清理作業會將所有空區塊集中在單一 Freelist。
4. FREELISTS = 最大並發插入 Session 數 (如 20)。
5. 非 RAC 環境 FREELIST GROUPS 忽略。

總結

調優無銀彈，但有章法：先保障 OS 資源 → 降低 I/O (重排/分區/索引) → 優化 SQL (四大法則) → 記憶體排序 → 微調儲存參數。每層突破都能帶來可量化的效能提升。