Oracle 系統優化 80% 價值來自 **SQL 語句調優**。本文聚焦 WHERE 子句寫法導致的索引失效陷阱、執行計畫分析方法論、優化器統計資訊管理、綁定變數窺探與自適應游標共享等核心技術，提供可直接套用的「調優檢查清單」與實戰案例。
-- ❌ 索引不存 NULL，全表掃描
SELECT * FROM emp WHERE comm IS NULL;
-- ✅ 改用函數索引 (11g+)
CREATE INDEX idx_emp_comm_nvl ON emp (NVL(comm, -1));
SELECT * FROM emp WHERE NVL(comm, -1) = -1;原則：B-tree 索引不儲存全 NULL 鍵值，任何包含 NULL 的複合索引鍵該行不入索引。
-- ❌ 連接導致索引失效
SELECT * FROM emp WHERE first_name || ' ' || last_name = 'George Bush';
-- ✅ 拆分條件，各列可用索引
SELECT * FROM emp WHERE first_name = 'George' AND last_name = 'Bush';
-- ❌ 函數/運算式
SELECT * FROM emp WHERE UPPER(ename) = 'SMITH';
-- ✅ 函數式索引 (FB Index)
CREATE INDEX idx_emp_upper_ename ON emp (UPPER(ename));
SELECT * FROM emp WHERE UPPER(ename) = 'SMITH';-- ❌ 開頭 % 無法用索引範圍掃描
SELECT * FROM emp WHERE ename LIKE '%SMITH%';
-- ✅ 後置 % 可用索引
SELECT * FROM emp WHERE ename LIKE 'SMITH%';
-- ✅ 含 % 但需模糊匹配 → Oracle Text / ctxsys.context 索引-- ❌ 排序需額外 SORT ORDER BY 操作
SELECT * FROM emp ORDER BY sal + comm;
-- ✅ 建立函數式索引或派生欄位
CREATE INDEX idx_emp_total_pay ON emp (sal + NVL(comm,0));
SELECT * FROM emp ORDER BY sal + NVL(comm,0);-- ❌ 否定條件通常全表掃描
SELECT * FROM emp WHERE sal != 3000;
-- ✅ 改寫為範圍查詢 (可用索引)
SELECT * FROM emp WHERE sal < 3000 OR sal > 3000;
-- ✅ 或使用 NOT EXISTS 替代 NOT IN (見下)-- ❌ IN 子查詢：先執行子查詢建臨時表，再驅動主查詢
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK');
-- ✅ EXISTS：相關子查詢，主查詢驅動，早期終止
SELECT * FROM emp e WHERE EXISTS (
SELECT 1 FROM dept d WHERE d.deptno = e.deptno AND d.loc = 'NEW YORK'
);
-- ✅ NOT EXISTS 替代 NOT IN (避免 NULL 陷阱)
SELECT * FROM emp e WHERE NOT EXISTS (
SELECT 1 FROM dept d WHERE d.deptno = e.deptno AND d.loc = 'BOSTON'
);效能差異：EXISTS 適合「外大內小」、有索引支援；IN 適合「外小內大」、子查詢結果可快取。現代優化器 (12c+) 常自動轉換，但複雜查詢建議顯式寫 EXISTS。
-- 方法 A: EXPLAIN PLAN (不執行，估算)
EXPLAIN PLAN FOR SELECT * FROM emp WHERE deptno = 10;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 方法 B: AUTOTRACE (執行並顯示統計)
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SELECT * FROM emp WHERE deptno = 10;
-- 方法 C: 實際執行計畫 (含實際行數、耗時) - 10g+
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));| 指標 | 含義 | 健康閾值 |
|---|---|---|
| Operation | 訪問路徑 (INDEX RANGE SCAN / FULL TABLE SCAN / HASH JOIN) | 核心表走索引、大表走 Hash Join |
| Cost | 優化器估算成本 (CPU+I/O) | 相對比較，無絕對標準 |
| Cardinality (Rows) | 估算返回行數 | 接近實際行數 (A-Rows) |
| E-Rows vs A-Rows | 估算 vs 實際 | 偏差 > 10x 表示統計過期/綁定窺探問題 |
| Buffers (Consistent Gets) | 邏輯讀次數 | 越低越好 |
| Predicates (Access/Filter) | 存取謂詞 vs 過濾謂詞 | Access 謂詞用索引，Filter 謂詞在索引後過濾 |
-- 檢查表/索引統計新鮮度
SELECT table_name, last_analyzed, num_rows, blocks
FROM user_tables
WHERE stale_stats = 'YES'; -- 11g+ 自動標記
-- 手動收集 (建議使用 DBMS_STATS.AUTO_SAMPLE_SIZE)
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER, tabname=>'EMP',
estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO', cascade=>TRUE);OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE (SPM)、/*+ BIND_AWARE */、CURSOR_SHARING=FORCE (慎用)SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_id='...';-- 捕捉優良計畫為 Baseline
EXEC DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'abc123');
-- 鎖定計畫防止退化
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle=>'SYS_SQL_...', plan_name=>'...', attribute_name=>'FIXED', attribute_value=>'YES');SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(...)) 或 AWR Top SQLSTALE_STATS=YES 表立即收集V$SQL.IS_BIND_SENSITIVE-- 原 SQL (全表掃描 + 排序)
SELECT * FROM sales
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY amount DESC;
-- 優化步驟:
-- 1. 建立複合索引 (範圍+排序)
CREATE INDEX idx_sales_date_amt ON sales (sale_date, amount DESC);
-- 2. 收集統計
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SALES', cascade=>TRUE);
-- 3. 驗證計畫: INDEX RANGE SCAN DESCENDING + NO SORT
-- 耗時: 45s → 0.8s系統化掌握 WHERE 子句反模式、執行計畫解讀、統計管理與進階優化機制，並建立「識別→分析→優化→驗證→固化」閉環流程，是 Oracle DBA 從「會調優」進階到「精準調優」的關鍵分水嶺。
