Oracle SQL_TRACE 性能跟踪

mahan · 发表于 4 天前

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

SQL 执行细节
性能瓶颈定位
递归调用追踪
核心特点

5807969eded8619f29.png (10.43 KB, 下载次数: 0)

4 天前上传

⚠️ 注意事项
启用跟踪会增加系统开销，使用后应立即关闭
生产环境避免全局启用，优先使用会话级跟踪
二、启用与停止方法
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 文件类型说明

登录/注册后可看大图

7915869ededa3df017.png (7.6 KB, 下载次数: 0)

下载附件

4 天前上传

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 常用参数

登录/注册后可看大图

2116369ededb6f211c.png (17.16 KB, 下载次数: 0)

下载附件

4 天前上传

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 输出指标说明

登录/注册后可看大图

8737369ededcca0020.png (26.72 KB, 下载次数: 0)

下载附件

4 天前上传

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)

登录/注册后可看大图

7930469ededdd80267.png (7.02 KB, 下载次数: 0)

下载附件

4 天前上传

六、最佳实践
6.1 跟踪策略选择

登录/注册后可看大图

8908069ededee0844a.png (9.38 KB, 下载次数: 0)

下载附件

4 天前上传

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（加索引、改写、绑定变量）                │
└─────────────────────────────────────────────────────────┘
七、常见问题排查

登录/注册后可看大图

帐号		自动登录	找回密码
密码			注册

[Oracle] Oracle SQL_TRACE 性能跟踪