Oracle 客戶端程式 SQL 追蹤與效能分析完整步驟

追蹤流程六步驟

1. 識別目標 Session：查詢 v$session 取得 SID、SERIAL#、MACHINE、PROGRAM、LOGON_TIME 等資訊，鎖定特定客戶端連線。
2. 設定追蹤參數：啟用 TIMED_STATISTICS、指定 USER_DUMP_DEST、設定 MAX_DUMP_FILE_SIZE 防止佔滿磁碟。
3. 啟動 SQL Trace：呼叫 DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION。
4. 收集負載樣本：讓系統運行一段代表性時間。
5. 關閉追蹤：再次呼叫 SET_SQL_TRACE_IN_SESSION 關閉。
6. 格式化分析：使用 TKPROF 將追蹤檔轉為可讀報表。

關鍵指令範例

1. 查詢 Session 資訊

SET LINESIZE 190 PAGESIZE 500
COL MACHINE FORMAT A30 WRAP
COL PROGRAM FORMAT A40
COL USERNAME FORMAT A15 WRAP
SELECT s.SID, s.SERIAL#, s.USERNAME, s.MACHINE, s.PROGRAM,
       p.SPID SERVPID, s.SERVER, s.LOGON_TIME
FROM V$SESSION s, V$PROCESS p
WHERE p.ADDR = s.PADDR;

2. 設定 Session 級參數

EXEC SYS.DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(8, 3, 'TIMED_STATISTICS', TRUE);
EXEC SYS.DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION(8, 3, 'MAX_DUMP_FILE_SIZE', 2147483647);
-- USER_DUMP_DEST 為系統級參數，建議使用預設值

3. 啟動/關閉追蹤

EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(8, 3, TRUE);  -- 啟動
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(8, 3, FALSE); -- 關閉

4. TKPROF 格式化常用參數

TKPROF trace.trc output.txt EXPLAIN=SCOTT/TIGER SYS=NO
-- 依查詢/執行/擷取時間排序
TKPROF trace.trc sort.prf EXPLAIN=SCOTT/TIGER PRINT=10 SORT='(PRSQRY,EXEQRY,FCHQRY)'
-- 依 CPU 時間排序
TKPROF trace.trc sort_cpu.prf EXPLAIN=SCOTT/TIGER PRINT=10 SORT='(PRSCPU,EXECPU,FCHCPU)'

適用範圍與限制

• 僅對專用伺服器模式 的連線有效。
• 追蹤本身會增加系統負載，建議在非尖峰時段、限定時間執行。
• Oracle 10g+ 建議改用 DBMS_MONITOR 套件與 TRCSESS 整合多檔案追蹤。