Oracle SQL 性能優化 21 條鐵律：從執行計劃分析到索引策略、改寫技巧與系統參數調優全景指南

核心優化原則

原則 1：WHERE 條件順序優化

Oracle 解析器自下而上處理 WHERE 子句，**表連接條件優先**，**過濾性最強條件最後** (最接近表名)。

-- 優化前：弱過濾在前
SELECT * FROM orders o, customers c
WHERE o.cust_id = c.cust_id
AND o.status = 'ACTIVE'          -- 弱過濾
AND c.region = 'APAC';           -- 強過濾

-- 優化後：強過濾下沉
SELECT * FROM orders o, customers c
WHERE c.region = 'APAC'          -- 強過濾優先
AND o.cust_id = c.cust_id        -- 連接條件
AND o.status = 'ACTIVE';         -- 弱過濾最後

原則 2：避免 SELECT *

• 增加解析開銷 (查詢數據字典展開列名)
• 阻礙索引覆蓋掃描
• 網絡傳輸無效數據
• 應用層依賴列順序風險

原則 3：索引使用鐵律

原則 4：索引選擇性與代價平衡

• 檢索比例 > 30% 表行數 → 全表掃描更優
• 每個索引增加 INSERT/UPDATE/DELETE 4-5 倍 IO 開銷
• 定期監控 V$OBJECT_USAGE 清理未用索引

SQL 改寫模式庫

1. EXISTS 替代 DISTINCT

-- 低效: 去重排序開銷大
SELECT DISTINCT d.deptno, d.dname 
FROM dept d, emp e 
WHERE d.deptno = e.deptno AND e.job = 'CLERK';

-- 高效: 半連接，滿足即停止
SELECT d.deptno, d.dname 
FROM dept d 
WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno = d.deptno AND e.job = 'CLERK');

2. UNION ALL 替代 OR (索引列)

-- 低效: OR 導致全表掃描
SELECT * FROM location WHERE loc_id = 10 OR region = 'MELBOURNE';

-- 高效: 兩次索引範圍掃描合並
SELECT * FROM location WHERE loc_id = 10
UNION ALL
SELECT * FROM location WHERE region = 'MELBOURNE';

3. UNION ALL 替代 UNION (允許重複時)

-- 低效: UNION 觸發排序去重
SELECT col FROM table1 UNION SELECT col FROM table2;

-- 高效: 無排序開銷
SELECT col FROM table1 UNION ALL SELECT col FROM table2;

4. ORDER BY 索引列 (最好是主鍵)

-- 低效: 排序非索引列
SELECT dept_code FROM dept ORDER BY dept_type;

-- 高效: 索引順序掃描無排序
SELECT dept_code FROM dept ORDER BY dept_code;

5. WHERE 替代 HAVING (分組前過濾)

-- 低效: 分組後過濾
SELECT job, AVG(sal) FROM emp 
GROUP BY job 
HAVING job = 'PRESIDENT' AND AVG(sal) > 5000;

-- 高效: 分組前過濾減少參與行
SELECT job, AVG(sal) FROM emp 
WHERE job IN ('PRESIDENT', 'MANAGER')
GROUP BY job 
HAVING AVG(sal) > 5000;

6. 避免子查詢 (能 JOIN 就 JOIN)

-- 子查詢開銷大
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE cond);

-- JOIN 替代
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.cond;

7. 資源消耗操作優化

系統級調優參數

SGA 核心組件調優

-- 共享池: 解析/執行計劃緩存
ALTER SYSTEM SET SHARED_POOL_SIZE = 1G SCOPE=BOTH;

-- 數據緩存: 邏輯讀性能核心
ALTER SYSTEM SET DB_CACHE_SIZE = 2G SCOPE=BOTH;

-- 重做緩存: 通常無需調大
ALTER SYSTEM SET LOG_BUFFER = 10M SCOPE=SPFILE;

-- PGA: 排序/雜湊/並行操作
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 1G SCOPE=BOTH;

優化器相關參數

執行計劃分析實戰

獲取執行計劃

EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 實際執行計劃 (含運行時統計)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, 'ALLSTATS LAST'));

關鍵指標解讀

統計信息管理策略

-- 1. 表級收集 (含直方圖)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'SCOTT', 
    tabname => 'EMP', 
    method_opt => 'FOR ALL COLUMNS SIZE AUTO',
    cascade => TRUE,
    degree => 4
);

-- 2. 模式級收集
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');

-- 3. 鎖定統計信息 (防止自動任務覆蓋)
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCOTT', 'EMP');

-- 4. 導出/導入統計信息 (跨環境同步)
EXEC DBMS_STATS.EXPORT_SCHEMA_STATS('SCOTT', 'STAT_TAB', statown => 'SYS');
EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('SCOTT', 'STAT_TAB', statown => 'SYS');

避坑檢查清單 (上線前必查)