Oracle SQL Trace (10046 Event) 實戰區：啟用 Trace 導致執行計畫改變的誤導性能分析案例

SQL Trace 隱藏陷阱：啟用 Trace 瞬間改變執行計畫

10046 EVENT (Extended SQL Trace) 是診斷效能的利器，但鮮為人知的是：**啟用 Trace 會導致 Session 重新解析 SQL，可能產生不同執行計畫**，進而誤導效能分析。本文複現真實案例，展示綁定變數窺探與 Trace 互動導致計畫翻轉的完整過程。

測試環境

• Oracle 10.2.0.1 Windows (9i/10g 同樣存在，11g 未測)
• 測試表 T1：FLAG 欄位資料傾斜 (2000 個值各 ~25 筆，值 3000 有 10000 筆)
• 索引 T1_IDX(FLAG)，已收集直方圖統計 (METHOD_OPT='FOR ALL INDEXED COLUMNS')

測試 SQL

SELECT /*+ TESTSQL */ SUM(VALUE) FROM T1 WHERE FLAG = :V_FLAG;

現象複現步驟

1. 未開啟 Trace 時：執行計畫為 INDEX RANGE SCAN (針對 :V_FLAG=3000 這類高頻值不佳，但優化器依統計資訊選擇)
2. 開啟 10046 Trace 後：

   
   ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
   -- 或
   EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(WAITS=>TRUE, BINDS=>TRUE);
   

   再次執行相同 SQL，**執行計畫變為 FULL TABLE SCAN**
3. 關閉 Trace：計畫恢復 INDEX RANGE SCAN

根因分析

• 啟用 Trace 觸發 Cursor Invalidation / Re-parse
• 重新解析時進行 Bind Variable Peeking (綁定變數窺探)，窺探到當下綁定值 (如 3000)
• 優化器根據窺探值 (高頻值) 判斷 INDEX 代價過高，選擇 FULL TABLE SCAN
• 未開啟 Trace 時可能使用舊 Cursor (舊計畫)，或窺探到不同值

驗證查詢

-- 查看 Cursor 共享與解析狀態
SELECT SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, PARSE_CALLS, EXECUTIONS, 
       OPTIMIZER_ENV_HASH_VALUE
FROM V$SQL WHERE SQL_TEXT LIKE '%TESTSQL%';

-- 查看綁定變數窺探歷史
SELECT * FROM V$SQL_BIND_CAPTURE WHERE SQL_ID = '...';

解決方案與最佳實踐

推薦診斷流程 (無副作用)

1. 問題 SQL 識別：SELECT * FROM V$SQL WHERE ELAPSED_TIME/EXECUTIONS > 1e6
2. 取得實際計畫：SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('sql_id', child_number, 'ALLSTATS LAST'));
3. 分析等待事件：SELECT * FROM V$SESSION_WAIT WHERE SID = ... 或 ASH 報表
4. 若必須 Trace：DBMS_MONITOR.SESSION_TRACE_ENABLE + 先在測試環境複現

工具鏈推薦

• TKPROF：官方格式化
• OraSRP：免費、圖形化、支援 10046/10053
• TVD$XTAT：《Troubleshooting Oracle Performance》作者開發
• Hotsos Profiler：商業級、深度分析

結語

SQL Trace 是觀測工具，但觀測行為本身會改變系統狀態 (量子效應)。生產環境效能診斷首選 DBMS_XPLAN + ASH/AWR 無側邊效應方案；Trace 僅作為最後手段並在可控環境使用。

1. 上一篇文章： Oracle NVL 函數完全指南：語法、類型匹配、NVL2/COALESCE 進階用法與實戰範例
2. 下一篇文章： Oracle Datafile 管理全攻略：新增、自動擴展、Resize、Bigfile/Smallfile、Tempfile 實戰指令

Oracle 資料庫基礎

PL/SQL 區塊、預存程序與套件基礎入門

前言 PL/SQL (Procedural Language

Oracle 記憶體管理核心：LRU 鏈、髒 LRU、檢查點隊列完整機制

一、LRU 鏈：改良版最近最少使用算法 基本結構 Buf

遭遇大規模 Oracle 壞塊？資深 DBA 教你正確應對策略

前言：技術必須回歸業務場景 近期業界熱談「場景化運維」與「場

Oracle 實戰案例：Redo Log Group 全 ACTIVE 導致系統卡頓，根因竟是 RAID 5 硬碟損壞

環境與症狀 平台：SunOS 5.8 / Ultra-En