Oracle 客戶端程式 SQL 追蹤與調優完整步驟（一）：識別 Session 與參數設定

針對特定客戶端程式進行 SQL 追蹤是資料庫調優的核心技能。本系列三篇完整說明從識別 Session、設定參數、啟動追蹤、收集資料、關閉追蹤到格式化分析的全流程。第一篇著重於 Session 識別與追蹤參數配置。

完整追蹤流程概覽

1. 識別目標客戶端程式的資料庫連線（Session），取得 SID 與 SERIAL#。
2. 設定追蹤參數：時間統計、追蹤檔路徑、最大檔案大小。
3. 啟動追蹤功能。
4. 讓系統運行一段時間收集 SQL 執行資料。
5. 關閉追蹤功能。
6. 使用 tkprof 格式化追蹤檔，產生可讀報表。

步驟 1：識別目標 Session

以 sqlplus 連線執行查詢：

SET LINESIZE 190
COL MACHINE FORMAT A30 WRAP
COL PROGRAM FORMAT A40
COL USERNAME FORMAT A15 WRAP
SET PAGESIZE 500
SELECT s.sid, s.serial#, s.username, s.machine, s.program,
       p.spid AS servpid, s.server
FROM v$session s, v$process p
WHERE p.addr = s.paddr;

查詢結果關鍵欄位解析

依據 MACHINE、PROGRAM、LOGON_TIME 定位目標 Session，記下 SID 與 SERIAL# 供下一步使用。

步驟 2：設定追蹤參數

使用 DBMS_SYSTEM 套件在目標 Session 設定參數：

-- 開啟時間統計（可知 SQL 各階段耗時）
EXEC sys.dbms_system.set_bool_param_in_session(
    sid => 8, serial# => 3,
    parnam => 'timed_statistics', bval => TRUE);

-- 設定追蹤檔目錄（系統級參數，影響所有 Session，建議使用預設值）
-- ALTER SYSTEM SET user_dump_dest = 'c:\temp';  -- 謹慎使用
-- 查看目前預設值：
-- SHOW PARAMETER user_dump_dest

-- 設定最大追蹤檔大小（避免佔滿磁碟，單位：作業系統區塊，2147483647 為無限制）
EXEC sys.dbms_system.set_int_param_in_session(
    sid => 8, serial# => 3,
    parnam => 'max_dump_file_size', intval => 2147483647);

參數說明：

• timed_statistics：TRUE 時記錄 CPU/Elapsed 時間，FALSE 僅記錄邏輯讀/物理讀。
• user_dump_dest：系統級參數，修改會影響所有 Session，生產環境建議維持預設，事後再搬移追蹤檔。
• max_dump_file_size：設為最大值確保不會截斷，事後可手動清理。