Oracle SQL Trace 與 10046 事件：隱藏陷阱與硬解析陷阱深度解析

為何需要 Extended SQL Trace (10046 Level > 1)

SQL Trace 與 10046 事件是診斷 Oracle 效能問題的利器。Level 12 (含等待事件與綁定變數) 能提供：

• 實際執行計畫 (非估算)。
• CPU、多塊讀、單塊讀等時間分佈明細。
• 邏輯讀、物理讀、Fetch/Parse 次數等統計指標。
• 支援事後分析與線上進行中 Session 診斷。
• 生態工具鏈豐富：TKPROF、Trace Analyzer、orasrp、TVD$XTAT、Hotsos Profiler 等。

核心陷阱：開啟 Trace 觸發硬解析導致計畫變更

實戰發現：在 Session 已開啟 SQL Trace 狀態下執行 SQL，會強制重新解析。因綁定變數窺視 導致生成新的 Child Cursor，執行計畫可能與無 Trace 時截然不同，進而誤導分析判斷。此問題存在於 9i、10g，11g 待驗證。

完整複現實驗 (Oracle 10.2.0.1 Windows)

環境建置：建立資料傾斜表與直方圖

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; -- 49,796 筆，flag 0-1999 各約 25 筆
INSERT INTO t1 SELECT rownum, 3000, LPAD('x',1000,'x') FROM dba_objects WHERE rownum <= 10000; -- flag=3000 有 10,000 筆
COMMIT;
CREATE INDEX t1_idx ON t1(flag);
EXEC dbms_stats.gather_table_stats(ownname=>USER, tabname=>'T1', cascade=>TRUE, method_opt=>'FOR ALL INDEXED COLUMNS');
-- FLAG 欄位收集到 75 個 Bucket 的直方圖

無 Trace 狀態下：Bind Peeking 導致全表掃描

首次執行綁定 :v_flag=3000 (高頻值)，Oracle 窺視並生成 Child Cursor 0，選擇 Full Table Scan (Cost 765)。後續執行 :v_flag=300/500 (低頻值) 仍複用 Cursor 0，維持全表掃描，邏輯讀 8,575，雖然走 Index Range Scan 只要 27 邏輯讀卻未被選擇。

開啟 10046 Level 12 後：硬解析產生新 Child Cursor

EXEC sys.dbms_system.set_ev(sid, serial#, 10046, 12, '');

再次執行 :v_flag=410 (低頻值)，Trace 顯示 PARSE #1 ... mis=1 (硬解析)，執行計畫轉為 INDEX RANGE SCAN，邏輯讀驟降至 27。V$SQL 出現 Child Number 1，Plan Hash Value 變更。

根因確認：V$SQL_SHARED_CURSOR

SELECT * FROM v$sql_shared_cursor WHERE sql_id='8hdvb5nwfqm8r';

結果顯示 STATS_ROW_MISMATCH = Y，官方文件解釋：「現有統計資訊與現有 Child Cursor 不符」。開啟 Trace 導致優化器環境變更 (統計層面)，觸發硬解析並重新窺視綁定值，產生最優新計畫。

關鍵結論與建議

1. Trace 改變行為： 開啟 10046 事件會改變優化器環境，導致硬解析與計畫變更，Trace 結果反映的是「開啟 Trace 後」的執行狀態，而非原始問題狀態。
2. 診斷策略調整： 若疑似綁定窺視問題，應優先用 DBMS_XPLAN.DISPLAY_CURSOR 觀察現有 Child Cursor 計畫，或用 OPTIMIZER_USE_SQL_PLAN_BASELINES / ADAPTIVE_CURSOR_SHARING (11g+) 穩定計畫，而非盲目依賴 Trace。
3. DBMS_MONITOR 同樣受影響： 10g 官方套件 DBMS_MONITOR.SESSION_TRACE_ENABLE 行為一致。