Oracle SQL Trace 多種啟用方式完整指南

前言

SQL Trace 是診斷 Oracle 數據庫性能問題的核心手段。本文系統整理了針對「已知 Session ID」、「已知 OS Process ID」、「已知 SQL_ID」三類場景的多種啟用方法，並附帶實戰示例與輸出解讀要點。

常用輔助查詢腳本

以下腳本可預先建立，便於快速獲取追蹤所需標識符：

• tname.sql：查詢默認 Trace 文件路徑
  SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
• sinfo.sql：根據 SID 獲取 SERIAL#
  SELECT sid, serial# FROM v$session WHERE sid=&sid;
• spinfo.sql：根據 OS PID 獲取 SID、SERIAL#
  SELECT s.sid, s.serial# FROM v$process p, v$session s WHERE p.addr=s.paddr AND p.spid=&pid;
• sid.sql：獲取當前會話 SID
  SELECT sid FROM v$mystat WHERE rownum<2;

場景一：已知 Session ID (SID) 進行追蹤

1. DBMS_MONITOR 包（推薦，10g+）

啟用：

EXEC dbms_monitor.session_trace_enable(
  session_id => &sid,
  serial_num => &serial,
  waits => TRUE,
  binds => FALSE);

關閉：

EXEC dbms_monitor.session_trace_disable(
  session_id => &sid,
  serial_num => &serial);

參數說明：waits 等待事件、binds 綁定變量、plan_stat 執行計劃統計。

2. DBMS_SYSTEM.SET_EV（舊版兼容）

啟用：

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

關閉： 將 level 設為 0

EXEC dbms_system.set_ev(&sid, &serial, 10046, 0, '');

關鍵提示： Trace 文件屬於被追蹤的 Session，而非發起追蹤的 Session。需在被追蹤 Session 中查詢 v$diag_info 才能獲得正確路徑。

場景二：已知 OS Process ID (PID) 進行追蹤

1. ORADEBUG（實例級，需 SYSDBA）

ORADEBUG SETOSPID &pid
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
-- 執行目標操作 --
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
ORADEBUG TRACEFILE_NAME  -- 顯示生成的 trace 文件路徑

示例中展示了從 PARSE、EXEC、FETCH 到 WAIT、STAT 的完整 Trace 內容，可見索引全掃描、綁定變量傾印等細節。

2. ALTER SYSTEM SET EVENTS ... SQL_TRACE {process:&pid}

ALTER SYSTEM SET EVENTS 'sql_trace {process:&pid} level 12';
-- 執行目標操作 --
ALTER SYSTEM SET EVENTS 'sql_trace {process:&pid} off';  -- 關閉有延遲

適用於無法獲取 SID、只能在 OS 層面定位進程的場景。

場景三：已知 SQL_ID 進行追蹤

ALTER SYSTEM SET EVENTS 'sql_trace[sql:&sql_id] level 12';
-- 執行目標 SQL --
ALTER SYSTEM SET EVENTS 'sql_trace[sql:&sql_id] off';

注意事項：

• Trace 文件與 Session 關聯，同一 SQL_ID 在不同 Session 執行會生成多個 Trace 文件。
• 關閉追蹤對已建立連接的 Session 不生效，僅影響新建連接。

Trace 文件分析建議

1. 使用 tkprof 格式化：tkprof input.trc output.txt sort=exeela,fchela
2. 重點關注：PARSE/EXEC/FETCH 次數、CPU/ELAPSED 時間、WAIT 事件、ROWS 處理量、STAT 執行計劃。
3. 結合 DBMS_MONITOR 的 plan_stat 參數可直接在 Trace 中獲取執行計劃。