Generate and view Oracle trace files

1. trace file introduction:

Trace file is a subsequent text file of TRC, which records various SQL operations and the time consumed, based on the trace file, we can understand which sqls lead to system performance bottlenecks and adopt appropriate methods for optimization.

2. How to generate trace file:

1.First, use sqlplus to log on to Oracle.

Show parameter SQL _trace

Name type value

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

SQL _trace Boolean false

If the value is false, the system does not generate a trace file. Use the following operations to generate a trace file:

Alter session set SQL _trace = true;

Or: Alter system set SQL _trace = true;

2.After executing some SQL statements, stop generating the trace file. Alter SESSION (or system) set SQL _trace = false.

3. default path of the trace file. Select value from V $ parameter where name = 'user _ dump_dest'

My local search result is: C: \ oracle \ product \ 10.2.0 \ admin \ ora102 \ udump.

3. How to change the directory for storing trace files:

For versions earlier than Oracle 11 GB, alter system set user_dump_dest = 'd: \ oracle \ trace; (Note: the trace file is directly generated in the trace directory)

For Oracle 11g. alter system set user_diagnostic_dest = 'd: \ oracle \ trace '; (Note: the trace file is not directly generated in the trace directory. many other directories are generated under the trace directory.

The specific directory of the trace file is: D: \ oracle \ trace \ diag \ RDBMS \ orli11r2 \ orli11r2 \ trace. Where orli11r2 is Sid)

4. How to view the trace file:

If you directly read the trace file, it is hard to understand. It is like the following (I just copied a paragraph ):

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 #0 oacdty = 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

We can use the tool tkprof that comes with Oracle to convert and then view the trace file. The conversion is as follows:

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)

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