Oracle SQL 追蹤收集完整指南：會話級、遠端會話、10046 事件、TKPROF 分析

場景一：可修改應用代碼（嵌入追蹤）

ALTER SESSION SET TIMED_STATISTICS = TRUE;
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'POX20240101';  -- 8.1.7+
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
-- 執行目標業務代碼
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Level 詳解

場景二：不可修改應用（遠端會話追蹤）

先從 V$SESSION 取得目標 SID、SERIAL#。

方法 A：DBMS_SUPPORT（推薦，10g+ 內建）

EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(sid => 123, serial => 456, waits => TRUE, binds => TRUE);
-- 等待業務跑完
EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(sid => 123, serial => 456);

方法 B：DBMS_SYSTEM.SET_EV（通用）

EXEC SYS.DBMS_SYSTEM.SET_EV(123, 456, 10046, 12, '');  -- 開啟 Level 12
-- 業務執行
EXEC SYS.DBMS_SYSTEM.SET_EV(123, 456, 10046, 0, '');   -- 關閉

方法 C：DBMS_SYSTEM.SET_BOOL/INT_PARAM_IN_SESSION（設定參數）

EXEC SYS.DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(123, 456, 'TIMED_STATISTICS', TRUE);
EXEC SYS.DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(123, 456, 'MAX_DUMP_FILE_SIZE', 2147483647);

追蹤檔定位

1. 目錄：USER_DUMP_DEST 或 BACKGROUND_DUMP_DEST。
2. 檔名包含 OS 進程號（SPID）：

   SELECT p.spid
   FROM   v$process p, v$session s
   WHERE  p.addr = s.paddr
   AND    s.sid = 123
   AND    s.serial# = 456;

3. 若設定 TRACEFILE_IDENTIFIER，檔名中會含該字串，易於辨識。

分析工具：TKPROF

tkprof ora_prod_ora_12345.trc output.txt EXPLAIN=user/pass SYS=NO SORT=EXELA,FCHLA

• EXPLAIN：產生執行計畫（需提供帳號）。
• SYS=NO：排除 SYS 遞迴 SQL。
• SORT：按執行時間/獲取行數排序，快速定位慢 SQL。

共享伺服器/連線池環境特殊處理

• 共享伺服器：單一前端連線對應多個後端共享伺服器進程，需追蹤**所有相關共享伺服器進程**或使用 DBMS_MONITOR.SESSION_TRACE_ENABLE（11g+）支援服務/模組/動作維度啟用。
• 連線池：應用層邏輯連線複用，追蹤前需鎖定特定邏輯連線（設定 MODULE/ACTION 並用 DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE）。

最佳實務清單

1. 生產環境追蹤前**評估磁碟空間**，MAX_DUMP_FILE_SIZE 設為 UNLIMITED 需確保目錄有足夠配額。
2. 追蹤完成**立即關閉**，避免持續佔用 I/O。
3. TKPROF 輸出重點關注：EXECUTIONS、ELAPSED、ROWS、WAIT EVENTS、OPTIMIZER MODE。
4. 建立標準化追蹤腳本庫，納入變更管理流程。