Oracle 專家調優祕籍：外部環境、Row Resequencing、SQL 調優、排序與競爭調優

調優層級金字塔

1. 外部環境 (CPU/記憶體/網路) — 基石，瓶頸在這裡調 Oracle 無用。
2. Row Resequencing — 減少磁碟 I/O 核心手段。
3. SQL 調優 — 最大效益區，簡單規則大幅提升。
4. 排序調優 — 影響查詢與 DML 效能。
5. 競爭調優 — 表/索引參數影響 INSERT/UPDATE 吞吐。

1. 外部環境檢查

• CPU：運行隊列 > CPU 核心數 → 瓶頸。對策：增 CPU、關閉 Parallel Query。
• 記憶體：分頁發生 → 不足。對策：增 RAM、縮減 SGA、關閉 MTS。
• 網路：大量 Net8 通訊 → 延遲。監控工具：vmstat、glance、top、sar、Windows PerfMon。

2. Row Resequencing (行重排序)

原則：磁碟 I/O 為響應時間最大成分，慢 10,000 倍於邏輯讀。若交易透過 PK 索引範圍掃描存取多列，將表列按 PK 順序物理重排，可大幅減少物理讀。

實例

SELECT salary FROM employee WHERE last_name LIKE 'B%';

• 未重排：100 列分散不同區塊 → 100+ 物理讀。
• 重排後：列連續存放 → 1 次索引讀 + 2 次資料區塊讀 = 3 物理讀，減少 97%。

判斷指標

SELECT clustering_factor FROM dba_indexes WHERE index_name = 'IDX_NAME';
-- clustering_factor ≈ blocks  → 順序佳
-- clustering_factor ≈ rows    → 順序差，適合重排

工具

• CREATE TABLE new_t AS SELECT * FROM old_t ORDER BY pk_col; (CTAS)
• Oracle 9i+ 內建 DBMS_REDEFINITION / 線上重定義。

3. SQL 調優核心規則

• 使用綁定變數，避免硬解析。
• 避免 SELECT *，只取需要欄位。
• WHERE 條件不對欄位做函數/運算 (WHERE TRUNC(dt)=... → WHERE dt BETWEEN ...)。
• 利用 EXISTS 替代 IN (子查詢大時)。
• 適度建立組合索引，引導欄在前。
• 定期收集統計資訊 (DBMS_STATS)。

4. 排序調優

• SORT_AREA_SIZE (專用伺服器) / SORT_AREA_RETAINED_SIZE (MTS) 決定記憶體排序上限，超量寫入暫存表空間。
• 大排序建議：增大排序區、使用暫存表空間多檔案/多磁碟、啟用 SORT_MULTIBLOCK_READ_COUNT。
• 避免不必要排序：UNION ALL 代替 UNION、INDEX DESC 避免 ORDER BY ... DESC 排序。

5. 競爭調優 (FREELISTS / PCTFREE / INITRANS)

總結

由外而內、由粗到細：先保證硬體資源無瓶頸，再透過 Row Resequencing 解決大 I/O，接著 SQL 調優收割最大效益，最後微調排序與區塊競爭參數，構建穩定高效的 Oracle 系統。