Oracle SQL Trace 開啟、Trace 文件定位與 TKPROF 深度分析全流程指南

為何性能測試工程師必須精通 SQL Trace

性能瓶頸 80%+ 源自後台應用與資料庫。Spotlight/Enterprise Manager 發現慢 SQL 後，**Trace + TKPROF 是鐵證**，可量化 Parse/Execute/Fetch 各階段耗時、等待事件、I/O 統計，指導精準優化。

標準開啟流程

1. sqlplus /nolog → CONN / AS SYSDBA
2. SHOW PARAMETER sql_trace 確認現狀 (預設 FALSE)。
3. ALTER SESSION SET SQL_TRACE = TRUE (Session 級) 或 ALTER SYSTEM SET SQL_TRACE = TRUE (全庫，慎用)。
   進階參數：
   TIMED_STATISTICS=TRUE (收集耗時) → MAX_DUMP_FILE_SIZE=UNLIMITED (防截斷) → USER_DUMP_DEST 確認輸出目錄。
4. 執行目標 SQL / 業務流程。
5. SHOW PARAMETER user_dump_dest 定位 .trc 檔案 (命名格式 SID_ora_PID.trc)。
6. ALTER SESSION SET SQL_TRACE = FALSE **立即關閉**，避免持續開銷。
7. 退出 SQL*Plus。

TKPROF 格式化與分析

基本語法

tkprof input.trc output.txt [EXPLAIN=user/pass] [TABLE=schema.temp_table] [SYS=NO] [SORT=EXEELA]

關鍵參數：

• EXPLAIN：在輸出中附帶執行計畫。
• TABLE：指定暫存表用於 EXPLAIN PLAN，避免污染業務表。
• SYS=NO：過濾 SYS 遞迴 SQL，聚焦應用 SQL。
• SORT：排序欄位，常用 EXEELA (執行經過時間降序)、FCHELA (Fetch 經過時間)、PRSELA (解析經過時間)。

輸出關鍵欄位解讀

實戰分析步驟

1. 按 ELAPSED 排序，鎖定 Top SQL。
2. 檢視 ROW SOURCE OPERATION (啟用 EXPLAIN 後產出) 確認執行計畫、實際行數 vs 預估行數。
3. 關注 WAIT EVENTS 區段：db file sequential read (索引單塊讀)、db file scattered read (全掃多塊讀)、log file sync (Commit 等待) 等。
4. 對比 QUERY vs DISK 判斷緩衝區命中率；CURRENT 高提示 DML 熱點。

補充：10046 Event 等級對照

• Level 1：標準 Trace (同 SQL_TRACE=TRUE)。
• Level 4：+ 綁定變數值。
• Level 8：+ 等待事件。
• Level 12：Level 4 + 8 (最常用，含綁定值與等待)。

啟用方式：ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12' 或 DBMS_MONITOR.SESSION_TRACE_ENABLE(waits=>TRUE, binds=>TRUE) (10g+ 推薦)。

最佳實踐提醒

• **僅在專用連接** 追蹤，共用伺服器 Trace 內容混雜難分析。
• 生產環境**極謹慎**開啟系統級 Trace，優先用 Session 級或 DBMS_MONITOR 精準鎖定 SID。
• 定期清理 USER_DUMP_DEST 避免磁碟爆滿。

1. 上一篇文章： Oracle SQL 效能優化進階篇：五大核心原則與實戰技巧
2. 下一篇文章： Oracle 分區表效能優化實戰：範圍、雜湊、列表、複合分區完整建模與 I/O 對比驗證

Oracle 資料庫基礎

Oracle Data Pump (EXPDP/IMPDP) 完整實戰手冊：目錄對象、五種導出模式、REMAP 參數詳解

Data Pump 是 10g+ 替代 EXP/IMP 的高

Oracle 11gR2 RAC 網路規劃深度解析：Public、VIP、Private、SCAN IP 四大 IP 角色

架構概覽 11gR2 引入 SCAN (Single Cli

C# OracleParameter 錯誤「No size set for variable length data type」解決方案

使用 OracleDataAccess.Client 或 S

Oracle 僅能用 SYS/SYSTEM 登入？ORA-28009 原因與解決、誤刪日誌導致非系統用戶無法登入的慘痛教訓

現象一：SYS 登入報 ORA-28009 ERROR: