Oracle 9i SQL 執行計劃與追蹤診斷：AUTOTRACE 與 TKPROF 實戰技巧

SQL*Plus 內建診斷三大利器

AUTOTRACE 統計欄位深度解讀

• Parse（剖析）：語法/語義檢查、最佳化器決策。複雜查詢（>5 表）耗時長，建議用 ORDERED/RULE Hint 縮短
• Execute（執行）：實際執行計劃步驟耗時
• Fetch（存取）：將結果集傳回客戶端耗時

執行計劃閱讀核心技巧

1. 由內而外、由上而下：最縮排者最先執行
2. 關注 Cost、Cardinality、Bytes：Cost 非時間單位，為相對資源權重
3. 識別關鍵操作：
   • TABLE ACCESS FULL → 全表掃描
   • INDEX RANGE SCAN / UNIQUE SCAN → 索引存取
   • NESTED LOOPS / HASH JOIN / MERGE JOIN → 連接方法
   • SORT ORDER BY / SORT GROUP BY → 排序操作
4. 檢查 Predicate Information：ACCESS/ FILTER 條件下推情況

TKPROF：深度追蹤分析工具

啟用追蹤

-- Session 級
ALTER SESSION SET SQL_TRACE = TRUE;
-- 或
EXEC DBMS_SESSION.SET_SQL_TRACE(TRUE);

-- Instance 級（慎用）
ALTER SYSTEM SET SQL_TRACE = TRUE;

生成可讀報表

tkprof ora_sid_ora_12345.trc output.txt EXPLAIN=scott/tiger SYS=NO SORT=PRSNT,EXECNT,FCHCNT

SORT 選項：PRSNT（剖析 CPU）、EXECNT（執行 CPU）、FCHCNT（擷取次數）

TKPROF 報表關鍵指標

• Count：執行次數
• CPU/Elapsed：CPU 時間/經過時間
• Disk/Query/Current：物理讀/邏輯讀（一致性/當前模式）
• Rows：處理行數
• Misses in library cache：硬解析次數

實戰診斷流程

1. SET AUTOTRACE ON EXPLAIN 快速看計劃，識別 Full Scan、高 Cost
2. SET AUTOTRACE ON 看統計，確認 Physical Reads、Disk Sorts
3. 若問題複雜，啟用 SQL_TRACE → TKPROF 深度分析
4. 對比修改前後統計，量化優化效果

優秀開發者的標準

「不僅能寫出 SQL，更能打造執行迅速的 SQL。」

• 習慣在開發階段就跑 AUTOTRACE
• 建立基準測試腳本，自動化回歸驗證
• 理解 DBMS_XPLAN.DISPLAY_CURSOR 獲取實際執行計劃（含 Bind Variable Peeking）

掌握 AUTOTRACE 與 TKPROF，讓 SQL 效能優化從「憑感覺」進化為「憑數據」。