Oracle Trace文件生成及查看 (zz)

Oracle Trace文件生成及查看 (zz)

1.Trace file简介:

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

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)

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

http://blog.csdn.net/weiwenhp/article/details/6932835


Tracinga SQL session

[] Start session trace //z 2012-3-9 16:14:26 PM IS2120@CSDN

To start a SQL trace for the currentsession, execute:

ALTERSESSION SET sql_trace = true;

You can also add an identifier tothe trace file name for later identification:

ALTERSESSION SET sql_trace = true;

ALTERSESSION SET tracefile_identifier = mysqltrace;

[] Stop session trace

To stop SQL tracing for the currentsession, execute:

ALTERSESSION SET sql_trace = false;

[] Tracing other user'ssessions

DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSIONto trace problematic database sessions. Steps:

• Get the SID and SERIAL# for the process you want to trace.

SQL>select sid, serial# from sys.v_$session where ...

       SID   SERIAL#

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

         8     13607

• Enable tracing for your selected process:

SQL>ALTER SYSTEM SET timed_statistics = true;

SQL>execute dbms_system.set_sql_trace_in_session(8, 13607, true);

• Ask user to run just the necessary to demonstrate his problem.

• Disable tracing for your selected process:

SQL>execute dbms_system.set_sql_trace_in_session(8,13607, false);

• Look for trace file in USER_DUMP_DEST:

$cd /app/oracle/admin/oradba/udump

$ls -ltr

total8

-rw-r-----    1 oracle  dba         2764 Mar 30 12:37ora_9294.trc

[] Tracing an entiredatabase

To enable SQL tracing for the entiredatabase, execute:

ALTERSYSTEM SET sql_trace = true SCOPE=MEMORY;

To stop, execute:

ALTERSYSTEM SET sql_trace = false SCOPE=MEMORY;

[] Identifying trace files

Trace output is written to thedatabase's UDUMP directory.

The default name for a trace filesis INSTANCE_PID_ora_TRACEID.trc where:

• INSTANCE is the name of the Oracle instance,
• PID is the operating system process ID (V$PROCESS.OSPID); and
• TRACEID is a character string of your choosing.

[] Size of trace files

The trace file size is limited bythe parameter MAX_DUMP_FILE_SIZE. The unit of this parameter, if you don'tspecify the K or M option, is in OS block size.

Be sure this parameter is set to avalue high enough for your purpose (e.g. some MB). Of course this depends onthe amount and complexitiy of statements which have to be run while tracing. Ifthis value is set too low, possibly the dump file size limit will be reachedbefore the execution of the crucial statements and the trace file will beclosed before the interesting parts can be recorded in it.

On the other hand, when thisparameter is set to UNLIMITED (default value), if the program to be traced isworking forth and forth and the trace mode is not finished, the trace file cangrow without limit which means until the associated file system or disk isfull. A DBA can stop the trace of a session using the DBMS_MONITOR (10g andup), DBMS_SYSTEM or DBMS_SUPPORT package.

[] Formatting output

Trace output is quite unreadable.However, Oracle provides a utility, called TKProf,that can be used to format trace output.