oracle trace文件命名,Oracle Trace文件的生成及查看

最新推荐文章于 2021-08-08 10:28:28 发布

回忆宝箱

最新推荐文章于 2021-08-08 10:28:28 发布

阅读量889

点赞数

文章标签： oracle trace文件命名

Tracefile是Oracle数据库中用于记录SQL操作及其执行时间的文本文件，有助于诊断性能问题。通过设置sql_trace参数可以开启和关闭tracefile的生成。默认存储路径可通过user_dump_dest参数查询。使用TKPROF工具可以将tracefile内容转化为可读的执行计划。本文介绍了如何生成、查看和分析tracefile，以优化数据库性能。

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

1.Trace file简介:

Trace file(追踪文件)是以trc为后续的文本文件,它记录了各种sql操作及所消耗的时间等,根据trace文件我们就可以了解哪些sql导致了系统的性能瓶颈，进而采取恰当的方式调优.

2.怎么生成trace file:

1. 首先用sqlplus登陆Oracle.

show parameter sql_trace

Name Type Value

-------------------------------------------

sql_trace boolean false

如果value是false表示系统当前不会产生trace文件.采取如下操作让系统产生trace文件:

alter session set sql_trace=true;

或者:alter system set sql_trace=true;

2.执行一些sql语句后.停止产生trace文件.

alter session(或system) set sql_trace=false.

3.trace文件所在的默认路径.

SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest'

我本机的查找结果是:C:\ORACLE\PRODUCT\10.2.0\ADMIN\ORA102\UDUMP.

3.怎么更改trace文件的保存目录:

如果是oracle 11g 以下的版本则:alter system set user_dump_dest = 'd:\oracle\trace';(注意:trace文件就直接生成在trace目录下)

如果是oracle 11g.则alter system set user_diagnostic_dest = 'd:\oracle\trace';(注意:trace文件不会直接生成在trace目录下.trace目录下会生成其他很多目录.

trace文件的具体目录是:d:\oracle\trace\diag\rdbms\orli11r2\orli11r2\trace.其中的orli11r2是SID)

4.怎么查看trace文件:

如果直接看trace文件是很难看懂的.就是下面的样子(我只是随便复制一段):

1319423003070764

=====================

PARSING IN CURSOR #2 len=90 dep=1 uid=0 oct=3 lid=0 tim=1319423003070864 hv=673844243 ad='7ecb9458' sqlid='9g485acn2n30m'

select col#,intcol#,reftyp,stabid,expctoid from refcon$ where obj#=:1 order by intcol# asc

END OF STMTPARSE #2:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2544153582,tim=1319423003070864

BINDS #2: Bind#0oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxs

我们可以用oracle自带的工具TKPROF转化后再查看trace文件.转化后是下面的样子:

SQL ID : 0zzk39z279q41

SELECT version FROM product_component_version WHERE product LIKE 'Oracle%'

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 1 0.00 0.00 0 0 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 3 0.00 0.00 0 0 0 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 69

Rows Row Source Operation

------- ---------------------------------------------------

1 VIEW PRODUCT_COMPONENT_VERSION (cr=0 pr=0 pw=0 time=0 us cost=2 size=168 card=2)

1 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=2 size=110 card=2)

1 UNION-ALL (cr=0 pr=0 pw=0 time=0 us)

0 FIXED TABLE FULL X$VERSION (cr=0 pr=0 pw=0 time=0 us cost=0 size=55 card=1)

1 FIXED TABLE FULL X$VERSION (cr=0 pr=0 pw=0 time=0 us cost=0 size=55 card=1)

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

Oracle查看trace文件步骤

1.获得当前trace文件生成路径

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

----------------------------------------------------------------------------------

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文件内容为可读的输出结果

注意Oracle的命令一般末尾都不要带分号。除非写的是SQL语句。否则那个分号也会算进命令里面。当做命令的内容来执行。

C:\Documents and Settings\Administrator>tkprof f:\oracle\administrator\diag\rdbms\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

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

[sql]

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

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)

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

[img]http://dl2.iteye.com/upload/attachment/0094/0115/168f1a99-8cf4-3101-8e89-a623b3e3e6c2.jpg[/img]

比如我本机上的内容是：

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

TRACEFILE

----------------------------------------------------------------------------

e:\app\administrator\diag\rdbms\fsyd\fsyd\trace\fsyd_ora_8096.trc

C:\Documents and Settings\kington>tkprof e:\app\administrator\diag\rdbms\fsyd\fsyd\trace\fsyd_ora_8096.trc output=C:\fsyd_ora_8096.txt;

TKPROF: Release 11.1.0.6.0 - Production on 星期三 2月 19 18:54:13 2014

C:\Documents and Settings\kington>

转换后的 fsyd_ora_8096.txt 内容如下：

[img]http://dl2.iteye.com/upload/attachment/0094/0117/66ee3894-6d8e-3087-ba1c-98e2fb6d524a.jpg[/img]

TKPROF: Release 11.1.0.6.0 - Production on 星期三 2月 19 18:54:13 2014

Trace file: e:\app\administrator\diag\rdbms\fsyd\fsyd\trace\fsyd_ora_8096.trc

Sort options: default

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

count = number of times OCI procedure was executed

cpu = cpu time in seconds executing

elapsed = elapsed time in seconds executing

disk = number of physical reads of buffers from disk

query = number of buffers gotten for consistent read

current = number of buffers gotten in current mode (usually for update)

rows = number of rows processed by the fetch or execute call

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

Trace file: e:\app\administrator\diag\rdbms\fsyd\fsyd\trace\fsyd_ora_8096.trc

Trace file compatibility: 11.01.00

Sort options: default

1 session in tracefile.

0 user SQL statements in trace file.

0 internal SQL statements in trace file.

0 SQL statements in trace file.

0 unique SQL statements in trace file.

116 lines in trace file.

0 elapsed seconds in trace file.

已有 0 人发表留言，猛击->> 这里<

ITeye推荐