Oracle 10046 事件深度解析：從啟用追蹤到 TKPROF 分析的完整流程

什麼是 10046 事件？

10046 是 Oracle 內部診斷事件，俗稱「SQL Trace」。啟用後會生成 Trace 文件，記錄解析、綁定、執行、獲取、等待等全生命周期信息，是**單條 SQL 性能分析**的黃金標準。若是整體性能下降，請用 AWR/Statspack。

關鍵初始化參數 (會話級/系統級均可修改)

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

10046 事件等級詳解

實戰：生成並分析 Trace 文件

步驟 1：啟用 10046 Level 12

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

步驟 2：執行目標 SQL

SELECT code, name, parent_code, connect_by_isleaf, lev
FROM ghsj_xm.v_organdataset
WHERE level = 1
CONNECT BY PRIOR code = parent_code
START WITH code = '14142'
ORDER SIBLINGS BY code;

步驟 3：關閉追蹤

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

步驟 4：定位 Trace 文件

方法一：ADR 目錄下 grep

cd $ADR_BASE/diag/rdbms///trace
ls | grep 10046_test

方法二：查詢 V$PROCESS/V$SESSION

SELECT tracefile FROM v$process
WHERE addr = (SELECT paddr FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum=1));

步驟 5：TKPROF 格式化分析

tkprof   SYS=NO SORT=EXEELA,PRSELA

關鍵排序選項：EXEELA (執行耗時)、FCHELA (獲取耗時)、PRSELA (解析耗時)、EXEDSK (執行物理讀)。

TKPROF 輸出核心欄位解讀

等待事件摘要分析

TKPROF 末尾列出 Elapsed times include waiting on following events，重點關注：

• db file sequential/scattered read — 索引/全表掃描 I/O
• log file sync — 提交等待 Redo 寫入
• enq: TX - row lock contention — 行鎖競爭
• SQL*Net message from client — 客戶端思考時間 (通常可忽略)

進階技巧

1. 多會話追蹤：DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id, serial_num, TRUE, TRUE) (需 DBA 權限)
2. 系統級開啟：ALTER SYSTEM SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'; — 極度謹慎，會產生海量文件，僅用於無法復現的極難問題。
3. TRCSESS 合併：針對同一 SQL_ID 多會話的 Trace 文件合併分析。
4. OEM/Enterprise Manager：圖形化查看 Trace，支援 SQL Monitor 關聯。

避坑指南

• Trace 文件可能極大 (GB 級)，務必設置 MAX_DUMP_FILE_SIZE=UNLIMITED 並監控磁盤空間。
• Level 12 開銷明顯，**僅在問題復現窗口開啟**，測試完畢立即關閉。
• 綁定變量值僅在 Level 4/12 記錄，排查「綁定窺探失效」必開。
• TKPROF 默認不顯示遞歸 SQL (內部遞歸)，加 SYS=YES 可看但通常噪音大。

總結

10046 + TKPROF 是 Oracle 單 SQL 調優的「核磁共振」。熟練掌握 參數設置 → 等級選擇 → 文件定位 → TKPROF 參數 → 讀懂 CPU/ELAPSED/DISK/QUERY/WAITS 全鏈路，是高級 DBA/開發的核心基本功。