萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 10g/11g 存儲過程跟蹤診斷:DBMS_MONITOR 與事件 10046 實戰

原文提供的腳本為 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 / TUNEEM/報表/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二進制含義
10001基本 SQL 跟蹤 (解析/執行/獲取計數、CPU/經過時間)
40100綁定變量值 (Bind Variables)
81000等待事件 (Wait Events)
121100綁定 + 等待 (最常用)
1610000執行計劃統計 (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= 微秒級耗時)

生產環境安全策略

  1. 限制跟蹤範圍:僅針對特定會話/服務/模塊,禁止全實例 DBMS_MONITOR.DATABASE_TRACE_ENABLE (會產生海量 .trc 佔滿磁盤)。
  2. 設置最大文件大小:ALTER SYSTEM SET MAX_DUMP_FILE_SIZE = 100M SCOPE=BOTH; (10g+ 支援無限 UNLIMITED)。
  3. 定期清理:ADRCI> PURGE -AGE 168 -TYPE TRACE (清理 7 天前追蹤文件)。
  4. 敏感數據脫敏:綁定變量可能包含 PII (身份證、信用卡),導出前需脫敏或限制 BINDS=FALSE。
  5. 使用統一追蹤目錄: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
copyright © 萬盛學電腦網 all rights reserved