DB / Oracle / 性能诊断

Oracle SQL Trace 与 TKPROF

2026.09.06

Oracle SQL Trace 与 TKPROF

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

SQL Trace 是详细 SQL 执行追踪[1]：

配套工具：

  • 10046 事件
  • TKPROF（格式化）
  • trcsess（合并）

详细见：Oracle 10046 事件与 SQL Trace。

2. 启用 Trace

2.1 自身会话

ALTER SESSION SET sql_trace = TRUE;

-- 或 10046
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
-- level 1: 标准
-- level 4: 加绑定变量
-- level 8: 加等待事件
-- level 12: 加 4+8

-- 执行 SQL

ALTER SESSION SET sql_trace = FALSE;
ALTER SESSION SET EVENTS '10046 trace name context off';

2.2 其他会话

-- 11g+
BEGIN
  DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id => 123,
    serial_num => 456,
    waits => TRUE,
    binds => TRUE
  );
END;
/

-- 关闭
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 123, serial_num => 456);

2.3 客户端 ID

EXEC DBMS_SESSION.SET_IDENTIFIER('my_app');
EXEC DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('my_app');
-- 应用设置后自动 trace

3. Trace 文件

3.1 位置

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

3.2 命名

<SID>_ora_<SPID>.trc

4. TKPROF

4.1 使用

tkprof input.trc output.prf 
  explain=scott/tiger 
  sys=no 
  sort=execpu

4.2 参数

参数说明
explain执行计划
sys=no排除 SYS SQL
sort排序（execpu/fchpu）
print=N显示 N 条
aggregate=yes/no聚合

4.3 排序

  • execpu: 执行 CPU
  • fchpu: 获取 CPU
  • exeela: 执行时间
  • fchela: 获取时间

5. TKPROF 报告解读

5.1 SQL 概要

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.01          0         10          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0         10          0           1

5.2 字段

字段说明
count次数
cpuCPU 时间
elapsed实际时间
disk物理读
query一致性读
current当前模式读
rows行数

5.3 关注点

  • 高 query：逻辑读多
  • 高 disk：物理读多
  • Parse 多：硬解析多
  • elapsed >> cpu：等待

6. 执行计划

6.1 显示

Rows   Row Source Operation
-----  ---------------------------------------------------
   10  TABLE ACCESS FULL EMPLOYEES (cr=7 pr=0 pw=0 time=... )

6.2 字段

  • cr: 一致性读
  • pr: 物理读
  • pw: 物理写
  • time: 时间（微秒）

7. 等待事件

7.1 显示

WAIT #1: nam='db file sequential read' ela= 1234 file#=4 block#=1234 ...

7.2 分析

  • 频繁等待：瓶颈
  • 长等待：性能

8. 多会话 Trace

8.1 trcsess

trcsess output=combined.trc 
  clientid=my_app 
  *.trc

8.2 TKPROF

tkprof combined.trc combined.prf

9. 实战

9.1 调优慢 SQL

-- 1. 启用 trace
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

-- 2. 执行
SELECT ... FROM big_table WHERE ...;

-- 3. 关闭
ALTER SESSION SET EVENTS '10046 trace name context off';
# 4. TKPROF
tkprof orcl_ora_12345.trc report.prf explain=scott/tiger sys=no sort=exeela

# 5. 分析

9.2 调优 PL/SQL

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
EXEC my_proc;
ALTER SESSION SET EVENTS '10046 trace name context off';

10. 应用层 Trace

10.1 标识

-- 应用连接时设置
EXEC DBMS_SESSION.SET_IDENTIFIER('app_user_123');

10.2 启用

EXEC DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('app_user_123');

11. 常见坑与排错

11.1 找不到 trace 文件

-- 查找
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- 11g+
SELECT tracefile FROM v$process WHERE addr = (
  SELECT paddr FROM v$session WHERE sid = USERENV('SID')
);

11.2 TKPROF 报告空

# 1. 检查 trace 文件
# 2. sys=no 可能过滤太多
# 3. 检查 explain 用户

11.3 trace 文件过大

-- 1. 控制时段
-- 2. 限制 SQL
-- 3. trcsess 合并

12. 最佳实践

  1. level 12：完整信息
  2. TKPROF 排序：按时间
  3. sys=no：过滤系统
  4. 短时段 trace：精准
  5. trcsess 合并：多会话
  6. 结合 AWR：长期
  7. 结合 ASH：实时
  8. 定位慢 SQL：根因
  9. 测试验证：效果
  10. 清理 trace：空间

13. 参考资料

[1] Oracle Database SQL Tuning Guide 19c, “SQL Trace” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-trace.html