原文提供的腳本為 SQL Server sp_trace_create,非 Oracle 語法。Oracle 官方提供 DBMS_MONITOR 套件 (10g+) 與事件 10046 (SQL Trace) 進行存儲過程級、會話級、實例級跟蹤。本文糾正工具選擇,提供 Oracle 原生跟蹤完整指南:啟用方式、輸出解讀、TKPROF 格式化、生產環境安全策略。
Oracle 官方跟蹤工具體系
| 工具/包 | 適用範圍 | 權限需求 | 輸出格式 |
DBMS_MONITOR.SESSION_TRACE_ENABLE | 當前/指定會話 | DBA / ALTER SESSION | .trc 追蹤文件 |
DBMS_MONITOR.DATABASE_TRACE_ENABLE | 全實例 | DBA / ALTER SYSTEM | 所有會話 .trc |
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE | 服務/模塊/動作維度 | DBA | 符合條件的會話 .trc |
| 事件 10046 (ALTER SESSION SET EVENTS) | 當前會話精細控制 | ALTER SESSION | .trc 追蹤文件 |
DBMS_SQLTUNE / SQL Monitoring | 單條 SQL 實時監控 | DBA / TUNE | EM/報表/HTML |
方案一:DBMS_MONITOR 會話級跟蹤 (推薦,10g+)
-- 1. 獲取目標會話 SID/SERIAL# (從 V$SESSION 查詢)
SELECT sid, serial#, username, program, module, action
FROM v$session
WHERE username = 'APP_USER' AND program LIKE '%batch_job%';
-- 2. 啟用跟蹤 (含等待事件、綁定變量、執行計劃)
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id => 123,
serial_num => 456,
waits => TRUE, -- 記錄等待事件
binds => TRUE -- 記錄綁定變量值
);
-- 3. 執行目標存儲過程 / 業務流程
-- (在應用端觸發或手動 EXEC)
-- 4. 禁用跟蹤
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(
session_id => 123,
serial_num => 456
);
-- 5. 定位追蹤文件
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
-- 或查詢 USER_DUMP_DEST / DIAG_TRACE 目錄下最新 .trc 文件
方案二:事件 10046 精細控制 (經典、全版本)
-- 在目標會話中執行 (或登錄觸發器自動啟用)
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
-- LEVEL 12 = LEVEL 1 (基本) + LEVEL 4 (綁定變量) + LEVEL 8 (等待事件)
-- 執行存儲過程
EXEC my_package.my_procedure;
-- 關閉跟蹤
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
-- 11g+ 也可用數字事件語法
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
-- 等價於
ALTER SESSION SET EVENTS 'sql_trace level 12';
10046 Level 位掩碼詳解
| Level | 二進制 | 含義 |
| 1 | 0001 | 基本 SQL 跟蹤 (解析/執行/獲取計數、CPU/經過時間) |
| 4 | 0100 | 綁定變量值 (Bind Variables) |
| 8 | 1000 | 等待事件 (Wait Events) |
| 12 | 1100 | 綁定 + 等待 (最常用) |
| 16 | 10000 | 執行計劃統計 (Row Source Statistics) |
TKPROF 格式化分析追蹤文件
$ tkprod prod_ora_12345.trc prod_ora_12345.txt \
EXPLAIN=user/pass@db \
SYS=NO \
SORT=EXELA,FCHLA \
AGGREGATE=YES \
RECORD=report.sql
-- 關鍵參數:
-- EXPLAIN: 生成執行計劃 (需連接數據庫)
-- SYS=NO: 排除 SYS 遞歸 SQL (如空間管理、審計)
-- SORT: 排序鍵 (EXELA=執行經過時間, FCHLA=獲取經過時間, PRSCNT=解析次數)
-- AGGREGATE=YES: 合併相同 SQL 文本 (去除字面量差異)
-- RECORD: 生成非聚合的原始記錄文件
TKPROF 報告關鍵指標解讀
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 42
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=15 us)
1 INDEX UNIQUE SCAN EMP_PK (cr=2 pr=0 pw=0 time=10 us)
- count:調用次數
- cpu/elapsed:CPU 時間 / 經過時間 (秒)
- disk:物理讀塊數
- query:一致性獲取 (邏輯讀,含 UNDO)
- current:當前塊獲取 (DML 修改塊)
- rows:返回行數
- Row Source Operation:實際執行計劃行源統計 (含
time= 微秒級耗時)
生產環境安全策略
- 限制跟蹤範圍:僅針對特定會話/服務/模塊,禁止全實例
DBMS_MONITOR.DATABASE_TRACE_ENABLE (會產生海量 .trc 佔滿磁盤)。
- 設置最大文件大小:
ALTER SYSTEM SET MAX_DUMP_FILE_SIZE = 100M SCOPE=BOTH; (10g+ 支援無限 UNLIMITED)。
- 定期清理:
ADRCI> PURGE -AGE 168 -TYPE TRACE (清理 7 天前追蹤文件)。
- 敏感數據脫敏:綁定變量可能包含 PII (身份證、信用卡),導出前需脫敏或限制
BINDS=FALSE。
- 使用統一追蹤目錄:
DIAGNOSTIC_DEST 統一管理,避免分散在多個 USER_DUMP_DEST。
存儲過程專項跟蹤模板
CREATE OR REPLACE PROCEDURE trace_my_proc (p_session_id IN NUMBER DEFAULT NULL) IS
l_sid NUMBER;
l_serial NUMBER;
BEGIN
-- 自動獲取當前會話或指定會話
IF p_session_id IS NULL THEN
SELECT sid, serial# INTO l_sid, l_serial FROM v$mystat WHERE rownum=1; -- 近似
SELECT sid, serial# INTO l_sid, l_serial FROM v$session WHERE audsid = SYS_CONTEXT('USERENV','SESSIONID');
ELSE
SELECT sid, serial# INTO l_sid, l_serial FROM v$session WHERE sid = p_session_id;
END IF;
-- 啟用跟蹤
DBMS_MONITOR.SESSION_TRACE_ENABLE(l_sid, l_serial, TRUE, TRUE);
-- 記錄跟蹤文件路徑供後續分析
INSERT INTO trace_log (session_id, serial#, trace_file, start_time)
SELECT l_sid, l_serial, value, SYSTIMESTAMP
FROM v$diag_info WHERE name = 'Default Trace File';
COMMIT;
END;
/
-- 使用:EXEC trace_my_proc; -- 當前會話
-- 或: EXEC trace_my_proc(123); -- 指定 SID