Oracle查看trace文件步骤

最新推荐文章于 2023-08-02 10:14:07 发布

iteye_19543

最新推荐文章于 2023-08-02 10:14:07 发布

阅读量92

点赞数

CC 4.0 BY-SA版权

文章标签： oracle培训 oracle学习 cuug培训 ocp com

本文链接：https://blog.youkuaiyun.com/iteye_19543/article/details/82570124

本文详细介绍了如何通过SQL查询生成路径、开启session追踪、转换并解析trc文件来分析和优化SQL语句的执行计划，包括解析次数、CPU使用率、等待时间等关键指标，最终实现性能提升。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[img]http://dl2.iteye.com/upload/attachment/0094/1985/1e0678b2-01df-30c3-9c37-06f04e28a4ce.jpg[/img]
1.获得当前trace文件生成路径

SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

TRACEFILE
-------------------------------------------------------------------------------------------------------------------------------
f:\Oracle\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_1160.trc

2.开启当前session的trace

SQL> alter session set sql_trace=true;

SQL> select count(*) from t1;

3.转换trc文件内容为可读的输出结果

C:\Documents and Settings\Administrator>tkprof f:\oracle\administrator\diag\rdbm
s\orcl\orcl\trace\orcl_ora_1160.trc output=c:\aa.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期六 10月 6 23:51:07 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

4.打开c:\aa.txt, 就会看到关于SQL语句 ‘ select count(*) from t1;’ 的执行计划

********************************************************************************
SQL ID: 5bc0v4my7dvr5
Plan Hash: 3724264953
select count(*)
from
t1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.04 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.01 0 1070 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.06 0 1071 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1070 pr=0 pw=0 time=0 us)
72597 TABLE ACCESS FULL T1 (cr=1070 pr=0 pw=0 time=172543 us cost=298 size=0 card=66014)

********************************************************************************