Oracle SQL 追蹤與效能診斷實戰指南

前言

鎖定效能瓶頸的第一步是獲取 SQL 執行軌跡。Oracle 10g+ 提供 trcsess 整合多檔案追蹤，支援 Shared Server 與 Connection Pool 環境。本文彙總 Session 級、跨 Session 級追蹤啟用/關閉、輸出分析全流程。

一、可修改應用程式碼：Session 級嵌入式追蹤

ALTER SESSION SET TIMED_STATISTICS = TRUE;           -- 9i+ 可 Session 級
ALTER SESSION SET MAX_DUMP_FILE_SIZE = UNLIMITED;    -- 9i+ 防止截斷
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'POX20031031a'; -- 9i+ 易識別檔名
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
-- 業務邏輯執行區間
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

Level 詳解

建議 Level 12 獲取完整診斷資訊；確保 USER_DUMP_DEST 目錄空間充足。

二、不可修改程式碼：跨 Session 追蹤

從其他 Session 對目標 SID, SERIAL# 施加追蹤：

方法一：DBMS_SUPPORT（推薦，自動處理 Event 10046）

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);
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_SYSTEM.SET_EV（通用，需手動指定 Event）

EXEC SYS.DBMS_SYSTEM.SET_EV(sid, serial, 10046, 12, '');  -- 開啟 Level 12
-- 執行目標業務
EXEC SYS.DBMS_SYSTEM.SET_EV(sid, serial, 10046, 0, '');   -- 關閉

⚠️ DBMS_SUPPORT 可能未安裝（需執行 dbmssupp.sql），此時改用方法二。

三、定位 Trace 檔案

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

檔案位於 USER_DUMP_DEST 或 BACKGROUND_DUMP_DEST，檔名含 OS Process ID (SPID) 與 TRACEFILE_IDENTIFIER。

四、後續分析

1. TKPROF 格式化：tkprof tracefile.trc output.txt SYS=NO SORT=PRSWA,EXEWA
2. 關注：PARSE/EXEC/FETCH 次數、CPU/Elapsed、Rows、Consistent Gets、Physical Reads、Wait Events。
3. Shared Server 環境：使用 trcsess 依 SESSION ID 或 MODULE/ACTION 合併多個 Trace 檔再分析。

五、適用範圍說明

本文針對 **Dedicated Server** 模式。Shared Server 與 Connection Pool 需結合 DBMS_MONITOR（10g+）、trcsess、Client ID 追蹤，複雜度較高，另文專述。