Oracle SQL_TRACE 性能跟踪

SQL_TRACE 是 Oracle 数据库的核心诊断工具，通过捕获 SQL 执行细节帮助定位性能瓶颈。本文覆盖启用方法、文件管理、TKPROF 格式化及结果解读，助你快速掌握数据库性能诊断技能。

一、概述

SQL_TRACE 是 Oracle 提供的底层性能跟踪工具，用于捕获会话或数据库级别的 SQL 执行活动，生成跟踪文件（trace file），帮助分析：

• SQL 执行细节
• 性能瓶颈定位
• 递归调用追踪

核心特点

⚠️ 注意事项

• 启用跟踪会增加系统开销，使用后应立即关闭
• 生产环境避免全局启用，优先使用会话级跟踪

二、启用与停止方法

2.1 全局启用（⚠️ 不推荐生产环境）

-- 参数文件方式（需重启数据库）
sql_trace = true

-- 动态修改（立即生效）
ALTER SYSTEM SET sql_trace = true;
ALTER SYSTEM SET sql_trace = false;  -- 关闭

2.2 当前会话启用（最常用方式）

-- 1. 设置跟踪文件标识（便于查找）
ALTER SESSION SET tracefile_identifier = 'my_trace_20240301';

-- 2. 开始跟踪
ALTER SESSION SET sql_trace = true;

-- 3. 执行需要跟踪的 SQL
EXEC PORTFOLIO.ETL_TASK_PRO(20240301, 1);

-- 4. 结束跟踪
ALTER SESSION SET sql_trace = false;

2.3 跟踪其他会话

-- 1. 查询目标会话的 SID 和 SERIAL#
SELECT sid, serial#, username FROM v$session WHERE username = 'APP_USER';

-- 2. 开始跟踪
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, true);

-- 3. 停止跟踪
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, false);

三、跟踪文件管理

3.1 文件位置

-- 查看跟踪文件存放目录
SHOW PARAMETER user_dump_dest;

-- 示例输出
-- user_dump_dest = /oracle/app/oracle/diag/rdbms/climb/climb/trace

3.2 文件类型说明

3.3 快速定位当前会话的跟踪文件

SELECT s.sid,
       s.server,
       i.instance_name || '_' || 
       NVL(pp.server_name, NVL(ss.name, 'ora')) || '_' ||
       p.spid || '.trc' AS trace_file_name
FROM   v$instance i,
       v$session s,
       v$process p,
       v$px_process pp,
       v$shared_server ss
WHERE  s.paddr = p.addr
  AND  s.sid = pp.sid(+)
  AND  s.paddr = ss.paddr(+)
  AND  s.sid = &session_id;  -- 输入会话ID

四、TKPROF 格式化工具

TKPROF 将原始 .trc 文件转换为易读的格式化报告。

4.1 基本用法

tkprof input.trc output.txt

4.2 常用参数

4.3 实用示例

# 基础格式化
tkprof trace_ora_12345.trc report.txt

# 包含执行计划，排除系统 SQL
tkprof trace_ora_12345.trc report.txt explain=scott/tiger sys=no

# 按提取时间排序，显示等待事件
tkprof trace_ora_12345.trc report.txt sort=fchela waits=yes

五、TRACE 文件解读

5.1 输出指标说明

5.2 关键关注点

高 disk 值 → 存在大量物理读，可能缺少索引或全表扫描

高 elapsed / cpu 比值 → 存在大量等待事件，需进一步分析

高 Misses in library cache → 存在硬解析，考虑使用绑定变量

Rows 与 count 比例 → 单次返回行数，判断提取效率

5.3 执行计划解读

Rows     Row Source Operation
-------  ---------------------------------------------------
  10000  TABLE ACCESS FULL (cr=1500 pr=1500 pw=0 time=500)

六、最佳实践

6.1 跟踪策略选择

6.2 性能影响控制

• 跟踪期间 避免大规模并发测试
• 跟踪完成后 立即关闭（sql_trace=false）
• 使用 sys=no 过滤系统递归调用，减少输出量

6.3 文件管理

• 定期清理 user_dump_dest 目录（可配置 ADR 自动清理）
• 使用 tracefile_identifier 命名规范，便于识别

6.4 标准诊断流程

┌─────────────────────────────────────────────────────────┐
│  1. 启用会话跟踪（ALTER SESSION SET sql_trace=true）    │
│                          ↓                               │
│  2. 复现问题操作                                         │
│                          ↓                               │
│  3. 关闭跟踪（ALTER SESSION SET sql_trace=false）       │
│                          ↓                               │
│  4. 使用 TKPROF 格式化                                   │
│                          ↓                               │
│  5. 分析耗时、物理读、执行计划                           │
│                          ↓                               │
│  6. 优化 SQL（加索引、改写、绑定变量）                   │
└─────────────────────────────────────────────────────────┘

七、常见问题排查

八、总结

SQL_TRACE + TKPROF 是 Oracle 性能诊断的经典组合：

• SQL_TRACE：捕获原始执行数据
• TKPROF：格式化成可读报告
• v$session：定位会话和跟踪文件
• 执行计划：定位性能瓶颈根因

一句话口诀：开启 → 复现 → 关闭 → 格式化 → 分析 → 优化

掌握 SQL_TRACE，让你告别盲目调优，用数据驱动性能优化决策。