Oracle 10g trcsess 實用程式：高效整合 SQL 追蹤檔案

前言

在資料庫效能調校的過程中，追蹤低效率的 SQL 語句是核心任務之一。Oracle 10g 引入了全新的 trcsess 實用程式，它能依據 Session ID、模組名稱等條件，從海量追蹤檔案中有選擇地提取特定會話的追蹤資料，並整合儲存至單一檔案。這項功能在共享伺服器架構中極具價值，因為調度程序可能將同一用戶的請求分派給不同的共享伺服器進程，導致單一會話產生多個分散的追蹤檔。透過 trcsess，DBA 可輕鬆獲得完整、整合的會話追蹤視圖，大幅簡化分析工作。

一、 於應用程式碼中嵌入追蹤指令（可修改原始碼時）

若能修改應用系統原始碼，可直接在程式中植入以下 PL/SQL 區塊來控制追蹤範圍：

-- 1. 開啟會話級計時統計 (Oracle 8.1.7+)
ALTER SESSION SET TIMED_STATISTICS = TRUE;

-- 2. 設定追蹤檔大小無上限，防止截斷 (Oracle 9i+)
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;

-- 3. 指定追蹤檔識別碼，便於辨識 (Oracle 9i+)
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'POX20031031a';

-- 4. 開啟 10046 事件，Level 8 含等待事件資訊
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

-- [ 此處執行需追蹤的應用程式邏輯 ]

-- 5. 關閉追蹤
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

參數詳細解析

• TIMED_STATISTICS：預設 FALSE。開啟後記錄作業耗時。9i 前僅能在參數檔設定並重啟實例生效。
• MAX_DUMP_FILE_SIZE：設為 UNLIMITED 避免追蹤中斷，須確保 USER_DUMP_DEST 目錄空間充足。
• TRACEFILE_IDENTIFIER：自訂字串嵌入檔名，利於快速定位目標追蹤檔。
• Event 10046 Level：控制追蹤深度。
  • 0：關閉
  • 1：標準追蹤 (無綁定變數/等待資訊)
  • 4：Level 1 + 綁定變數
  • 8：Level 1 + 等待事件 (常用)
  • 12：Level 1 + 4 + 8 (完整資訊)

二、 從外部會話對指定 Session 進行追蹤（不可修改程式碼時）

透過 SYS.DBMS_SYSTEM 或 DBMS_SUPPORT 套件，在另一會話中啟動/停止目標會話的追蹤。需先從 V$SESSION 取得目標 SID 與 SERIAL#。

方法一：使用 DBMS_SUPPORT (Oracle 推薦，避免手寫事件號)

-- 設定參數
EXEC SYS.DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(:sid, :serial, 'TIMED_STATISTICS', TRUE);
EXEC SYS.DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(:sid, :serial, 'MAX_DUMP_FILE_SIZE', 2147483647);

-- 開啟追蹤 (waits=>TRUE 等同 Level 8/12)
EXEC SYS.DBMS_SUPPORT.START_TRACE_IN_SESSION(:sid, :serial, WAITS => TRUE, BINDS => FALSE);

-- [ 運行目標應用程式 ]

-- 關閉追蹤
EXEC SYS.DBMS_SUPPORT.STOP_TRACE_IN_SESSION(:sid, :serial);

注意：DBMS_SUPPORT 套件可能未安裝 (需執行 $ORACLE_HOME/rdbms/admin/dbmssupp.sql)，若不存在則改用方法二。

方法二：直接操作 10046 事件

-- 開啟 Level 8
EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 8, '');

-- [ 運行目標應用程式 ]

-- 關閉 (Level 0)
EXEC SYS.DBMS_SYSTEM.SET_EV(:sid, :serial, 10046, 0, '');

三、 尋找與分析追蹤檔案

追蹤檔位於 USER_DUMP_DEST (專用伺服器) 或 BACKGROUND_DUMP_DEST (背景進程)。檔名包含作業系統進程 ID (SPID)，可透過以下查詢定位：

SELECT p.SPID
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
  AND s.SID = :sid
  AND s.SERIAL# = :serial;

本文以專用伺服器模式為例。共用伺服器模式及連線池環境下的追蹤收集較為複雜，留待後續探討。