Oracle SQL 效能優化系列 (六) - 進階改寫技巧與診斷工具

前言

本系列第六部分聚焦實戰 SQL 改寫模式、低效 SQL 識別查詢、TKPROF 追蹤分析，幫助 DBA 與開發人員快速定位並解決效能瓶頸。

技巧 20：以表連接取代 EXISTS

原則： 在 RBO (Rule-Based Optimizer) 或早期 CBO 版本中，JOIN 常比 EXISTS 效率高，因後者執行路徑包含 FILTER 操作，前者可使用 NESTED LOOPS。

改寫前 (EXISTS)

SELECT ename
FROM emp e
WHERE EXISTS (
    SELECT 'X'
    FROM dept d
    WHERE d.deptno = e.deptno
      AND d.dept_cat = 'A'
);

改寫後 (JOIN)

SELECT e.ename
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE d.dept_cat = 'A';

現代 CBO (11g+) 建議： 優化器會自動轉換 EXISTS 為 SEMI JOIN，兩者效能通常等價。若 EXISTS 子查詢極其複雜，改寫為 JOIN 並加上 DISTINCT 或 /*+ NO_UNNEST */ Hint 可能更穩定。

技巧 21：以 EXISTS 取代 DISTINCT

場景： 一對多關聯查詢 (部門-員工) 需去重部門資訊。

低效 (DISTINCT)

SELECT DISTINCT d.deptno, d.dname
FROM dept d
JOIN emp e ON d.deptno = e.deptno;

高效 (EXISTS)

SELECT d.deptno, d.dname
FROM dept d
WHERE EXISTS (
    SELECT 'X'
    FROM emp e
    WHERE e.deptno = d.deptno
);

原理： EXISTS 找到第一筆符合即停止掃描 (Short-circuit)，避免 DISTINCT 的排序/雜湊去重開銷。CBO 會轉為 HASH JOIN SEMI 或 NESTED LOOPS SEMI。

技巧 22：識別低效 SQL (自訂診斷查詢)

從 V$SQLAREA 找出緩衝區命中率低、單次執行讀取量大的 SQL：

SELECT executions,
       disk_reads,
       buffer_gets,
       ROUND((buffer_gets - disk_reads) / buffer_gets, 2) AS hit_ratio,
       ROUND(disk_reads / executions, 2) AS reads_per_exec,
       sql_text
FROM v$sqlarea
WHERE executions > 0
  AND buffer_gets > 0
  AND (buffer_gets - disk_reads) / buffer_gets < 0.8  -- 命中率 < 80%
ORDER BY hit_ratio ASC, reads_per_exec DESC;

指標解讀：

• hit_ratio < 0.8：大量物理讀，可能缺乏索引、全表掃描、統計資訊過期。
• reads_per_exec > 1000：單次執行讀取過多區塊，需檢視執行計畫。
• 搭配 sql_id 進一步分析：SELECT * FROM v$sql_plan WHERE sql_id = '...';

技巧 23：使用 TKPROF 分析 SQL Trace

啟用追蹤

-- Session 級 (建議)
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET TIMED_STATISTICS = TRUE;  -- 必須開啟，否無時間統計
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;

-- 執行目標 SQL...

-- 關閉追蹤
ALTER SESSION SET SQL_TRACE = FALSE;

-- 系統級 (慎用，影響效能)
-- init.ora: sql_trace = true, timed_statistics = true, user_dump_dest = '/path'

轉換 Trace 檔

# 找到 trace 檔 (user_dump_dest 目錄下 *.trc)
tkprof orcl_ora_12345.trc orcl_ora_12345.txt \
  EXPLAIN=scott/tiger \
  TABLE=scott.temp_plan_table \
  SYS=NO \
  SORT=EXEELA,FCHELA

常用排序選項： PRSCNT (解析次數)、EXECNT (執行次數)、FCHCNT (擷取次數)、EXEELA (執行耗時)、FCHELA (擷取耗時)、QRCNT (查詢區塊數)。

TKPROF 輸出關鍵欄位

分析重點

1. 高 CPU / 高 ELAPSED：執行計畫不佳、缺乏索引、統計資訊錯誤。
2. 高 DISK / 低 QUERY：資料不在緩衝區、全表掃描、索引失效。
3. ROW 源操作統計：查看 TABLE ACCESS FULL、INDEX RANGE SCAN、HASH JOIN 實際啟動次數與列數。
4. 等待事件：db file sequential read (索引讀)、db file scattered read (全表掃描)、log file sync (Commit 等待)。

補充：現代診斷工具鏈 (11g+)

建議：建立標準化「SQL 效能檢核清單」納入 Code Review 流程：1. 有無全表掃描 2. 索引是否被使用 3. 統計資訊是否最新 4. Bind Variable 是否一致 5. 執行計畫是否穩定 (SQL Plan Baseline)。