Oracle 效能調優全攻略：外部環境、ROW 重排序、SQL 調優、排序優化、競爭控制

調優核心哲學

「調優目標單一：**減少 I/O**，特別是物理磁碟 I/O。磁碟存取比記憶體慢 10,000 倍。」

一、外部環境調優 (OS/硬體/網路)

CPU 瓶頸

• 現象：運行隊列 > CPU 核心數
• 對策：增加 CPU、關閉 Parallel Query、優化高耗 CPU SQL

記憶體分頁

• 現象：vmstat / sar -W 顯示大量 swap in/out
• 對策：增加實體記憶體、縮減 SGA、關閉 MTS (Shared Server)

網路延遲

• 現象：大量 Net8/TNS 封包往返
• 對策：啟用連線池、減少往返 (批次處理、Array Bind)、SDN/InfiniBand

原則：外部資源不足時，任何資料庫內部調優皆無效。先用 vmstat, top, glance, sar 確認 OS 層面健康。

二、Row Re-sequencing (列重排序) 減少磁碟 I/O

原理

若應用常透過 **主鍵索引範圍掃描** 讀取連續多列 (如 WHERE last_name LIKE 'B%')，將表資料按主鍵順序物理儲存，可將 100 次隨機 I/O 降為 3 次循序 I/O (1 次索引 + 2 次資料塊)。

判斷指標

SELECT clustering_factor, blocks, num_rows
FROM dba_indexes i
JOIN dba_tables t ON i.table_owner=t.owner AND i.table_name=t.table_name
WHERE i.index_name = 'PK_EMP';
-- clustering_factor ≈ blocks  → 順序良好
-- clustering_factor ≈ num_rows → 順序混亂，適合重排序

實作方式

1. CTAS (Create Table As Select)：
   CREATE TABLE emp_new TABLESPACE new_tbs AS SELECT * FROM emp ORDER BY empno;
   重建索引、約束、授權 → 更名切換
2. Oracle 9i+ 線上重定義 (DBMS_REDEFINITION)：零停機重組
3. 12c+ Attribute Clustering：CREATE TABLE ... CLUSTERING BY LINEAR ORDER (empno) ... 自動維護

效果

大表索引範圍掃描查詢效能可提升 **3 倍以上**。

三、SQL 調優 (佔調優工作 90%)

四大鐵律

1. 消除不必要的全表掃描：
   有序表回傳 <40%、無序表回傳 <7% → 應用索引。
   手段：建立 B-tree/位圖/函數索引、INDEX Hint、OPT_PARAM 調整開銷。
2. 小表全掃描緩存化：
   ALTER TABLE small_t CACHE; (7) 或放入 KEEP pool (8+)。
3. 確保最優索引使用：
   多索引時檢查 EXPLAIN PLAN，必要時 INDEX_COMBINE, INDEX_JOIN Hint 指定。
4. 確保最優聯結方法：
   Nested Loop (小驅動大)、Hash Join (大等值聯結)、Sort-Merge (大範圍/排序已就緒)。

調優流程

1. 識別高負載 SQL (V$SQL, AWR, ASH)
2. EXPLAIN PLAN / DBMS_XPLAN.DISPLAY_CURSOR 分析計畫
3. 檢查統計資訊新鮮度 (DBMS_STATS)
4. 應用上述四大鐵律逐項優化
5. 驗證：邏輯讀、物理讀、執行時間、CPU 時間

四、排序優化

觸發排序操作

• ORDER BY, GROUP BY, DISTINCT
• CREATE INDEX
• 聯結時索引不足觸發 MERGE JOIN

記憶體 vs 磁碟排序

• 專用連接：PGA 中 sort_area_size (手動) 或 PGA_AGGREGATE_TARGET (自動)
• 共用連接：LARGE_POOL
• 超出記憶體 → TEMP 表空間磁碟排序，**慢 14,000 倍**

優化策略

1. 設定適當 PGA_AGGREGATE_TARGET (10g+ 自動管理)
2. 避免不必要的 ORDER BY (應用層排序)
3. 大排序考慮 PARALLEL 分散至多進程
4. 監控 V$SORT_USAGE, V$SQL_WORKAREA 偵測磁碟排序

五、競爭控制 (鎖/鎖存/空間管理)

表/索引儲存參數

關鍵原則

• 空間重用 vs 效能：高 PCTUSED → 空間重用好但 I/O 多；低 PCTUSED → 效能好但空間浪費。**取決於業務模式**
• 避免 CHAINED ROWS：PCTFREE 過低或 DB_BLOCK_SIZE 過小 → 行跨塊 → 額外 I/O
• 同步插入/刪除：單純批次刪除會將所有空塊集中單一 freelist，後續插入競爭劇烈

RAC 環境

• FREELIST GROUPS = 實例數
• 使用 ASSM (Automatic Segment Space Management) 表空間，自動管理 freelist，參數多被忽略

總結：調優檢查清單

結語

Oracle 調優是系統工程：**外部資源是地基、物理設計是骨架、SQL 是血肉、排序與競爭是神經**。依序打穩每一層，以「減少物理 I/O」為唯一北極星，才能在複雜負載下持續交付穩定效能。