Oracle SQL Trace (10046) 正確啟用與執行計畫變更陷阱

10046 事件價值

• 取得 實際執行計畫 (非 EXPLAIN PLAN 推估)。
• 詳細時間分佈：CPU、等待事件 (db file scattered read 等)、Fetch/Parse/Execute 次數。
• 適用單一 SQL、PL/SQL、Session 級效能診斷。
• 工具鏈豐富：TKPROF、Trace Analyzer、ORASRP、TVD$XTAT、Hotsos Profiler。

隱藏陷阱：啟用 Trace 導致重新解析，執行計畫可能改變

開啟 10046 trace 的 Session，Oracle 會將後續 SQL 標記為需重新優化，導致 執行計畫與未 Trace 時不同，分析結果失真。

實測重現 (Oracle 10.2.0.1 / 9i 均存在)

環境建置

-- 建立資料傾斜表
CREATE TABLE t1 (value NUMBER, flag NUMBER, pad VARCHAR2(2000));
INSERT INTO t1 SELECT rownum, MOD(rownum,2000), LPAD('x',1000,'x') FROM dba_objects;
INSERT INTO t1 SELECT rownum, 3000, LPAD('x',1000,'x') FROM dba_objects WHERE rownum <= 10000;
COMMIT;
CREATE INDEX t1_idx ON t1(flag);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T1',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS');
-- FLAG 欄位有 2030 個 distinct value，並建立直方圖 (75 buckets)

測試 SQL

SELECT /*+ testsql */ SUM(value) FROM t1 WHERE flag = :v_flag;

現象

• 未開 Trace：首次執行產生計畫 (可能 Full Table Scan 或 Index Range Scan)，後續綁定變數窺探固定計畫。
• 開啟 Trace 後：強制重新解析，優化器可能選擇不同存取路徑，導致 Trace 記錄的計畫非真實生產計畫。

正確啟用流程 (避免污染)

1. 預先暖機：在開啟 Trace 前，先用相同綁定值執行數次目標 SQL，使計畫穩定。
2. 設定 Session 參數：

   ALTER SESSION SET TIMED_STATISTICS = TRUE;
   ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
   ALTER SESSION SET TRACEFILE_IDENTIFIER = 'MY_TRACE_20240101';

3. 開啟 Trace (Level 12 含 Bind + Wait)：

   ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

4. 執行目標 SQL / 程式碼。
5. 關閉 Trace：

   ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

6. 定位 Trace 檔：

   SELECT p.spid
       FROM   v$process p, v$session s
       WHERE  p.addr = s.paddr AND s.sid = SYS_CONTEXT('USERENV','SID');

   檔案位於 USER_DUMP_DEST 或 BACKGROUND_DUMP_DEST，檔名含 spid 與 TRACEFILE_IDENTIFIER。
7. 格式化分析：tkprof trace.trc out.txt EXPLAIN=user/pass SYS=NO SORT=EXEELA,FCHELA。

跨 Session 追蹤 (無法修改應用程式碼時)

-- 方法一：DBMS_SUPPORT (推薦，需安裝)
EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(sid, serial#, WAITS=>TRUE, BINDS=>TRUE);
-- ... 運行應用 ...
EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(sid, serial#);

-- 方法二：DBMS_SYSTEM (通用)
EXEC SYS.DBMS_SYSTEM.SET_EV(sid, serial#, 10046, 12, '');
-- ... 運行應用 ...
EXEC SYS.DBMS_SYSTEM.SET_EV(sid, serial#, 10046, 0, '');

結語

SQL Trace 是效能調優利器，但 啟用 Trace 本身會改變行為。務必先暖機固定計畫，再開啟 Trace，才能獲得真實可信的診斷資料。