Oracle SQL_TRACE 性能跟踪

mahan

一、概述
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，让你告别盲目调优，用数据驱动性能优化决策。