Oracle SQL Trace Usage

Sql_trace is a very good tracking tool provided by Oracle, primarily used to check the database for anomalies, and to track the activity of the database to find the problematic statement.

First, overview:
Sql_trace is a very powerful tool for Oracle. Open Sql_trace to gradually capture the database activity of any one session, or capture the activity of the entire database and log the database activity as a trace file. You need to turn off tracing after each use, otherwise it will degrade the performance of the system.
Sql_trace can help diagnose many problems, including:

Second, usage:
1, the classification of file tracking:
Trace DBAs can be tracked in two ways:
. Tracks the entire database instance. Simply modify the parameter file (pfile/spfile) parameter sql_trace = TRUE, and then restart the database. Enabling Sql_trace globally causes the activity of all processes to be tracked, including background processes and all user processes, which can also lead to significant performance degradation in the database.
. Session-level tracing. The usual way to use Sql_trace is to track only one session. The tracked session can be either your own or another user's session. If it's your own session, just run the command in Sql*plus:
Sql> alter session Set sql_trace = true;
Similarly, if you cancel the tracing of the session, run the command:
Sql> alter session Set Sql_trace = FALSE;

If you need to track a particular session, you first need to get the SID and Serial# of the session, which can be obtained in the view v$session, and once you know the two parameters, you can run the command:
Sql> Execute SYS.dbms_system.set_sql_trace_in_session (13,9,true);
You can also use this procedure to turn off session tracking:
Sql> Execute SYS.dbms_system.set_sql_trace_in_session (13,9,false);

2, the location of tracking files:
Once the sql_trace,oracle is activated for the session, the trace file is created in the Udump admin area, and the target location of the file is determined by the parameter user_dump_dest. Each action does not overwrite the original file, and the new trace record will be appended to the end of the file. In general, you can determine which file under the directory is the most recent file, depending on when the file was modified.
Sql> Show parameter user_dump_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
User_dump_dest string D:oracleadminora9iudump

You can also use the following SQL to determine the file name:

Select D.value| | | | Lower (RTrim (i.instance, Chr (0))) | | _ora_ ' | | p.spid| | '. TRC ' Trace_file_name
From
(
Select P.spid
From Sys.v$mystat m,sys.v$session s,sys.v$process p
where m.statistic# = 1
and S.sid = M.sid
and p.addr = S.paddr
) p,
(
Select T.instance
From Sys.v$thread T,sys.v$parameter V
where v.name = ' thread '
and (V.value = 0 or t.thread# = To_number (v.value))
I
(
Select value from sys.v$parameter where name = ' User_dump_dest '
) D;

Trace_file_name
--------------------------------------------------------------------------------

D:oracleadminora9iudumpora9i_ora_2060.trc

3. Timing information:
In order to maximize the use of trace files, you should turn on the timing flag, set by the parameter timed_statisticts=true, so that the execution time of each SQL statement can be recorded, the function of the system performance is very small.
To open a session's timing information:
Sql> alter session Set timed_statistics = true;
Turn on timing information for the database system
Sql> alter system set Timed_statistics = true;

4, TKPROF:
With the first three steps already known how to generate SQL Trace files, Oracle generated trace files are difficult to read (that is, poor readability), you can look at the part of the trace file, execute the following SQL statement:
Sql> Select COUNT (*) from sys_dept;

COUNT (*)
----------
16
When you're done, look at the contents of this statement in the trace file as follows:

Parsing in CURSOR #1 len=31 dep=0 uid=62 oct=3 lid=62 tim=14727407741 hv=2200985491 ad= ' 128e3820 '
Select COUNT (*) from sys_dept
END of STMT
PARSE #1: c=0,e=16348,p=1,cr=31,cu=0,mis=1,r=0,dep=0,og=4,tim=14727407735
EXEC #1: c=0,e=24,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727407814
FETCH #1: c=0,e=15641,p=5,cr=7,cu=0,mis=0,r=1,dep=0,og=4,tim=14727423807
=====================
Parsing in CURSOR #2 len=61 dep=0 uid=62 oct=47 lid=62 tim=14727508742 hv=3517412409 ad= ' 12bbcff4 '
Begin:id: = sys.dbms_transaction.local_transaction_id; End
END of STMT
PARSE #2: c=0,e=122,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727508735
EXEC #2: c=0,e=144,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=14727508945
=====================
Parsing in CURSOR #2 len=61 dep=0 uid=62 oct=47 lid=62 tim=14727587562 hv=3517412409 ad= ' 12bbcff4 '
Begin:id: = sys.dbms_transaction.local_transaction_id; End
END of STMT
PARSE #2: c=0,e=121,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=14727587556
EXEC #2: c=0,e=97,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=4,tim=14727587721

This is not only a reading problem, but also a lot of content difficult to understand. Oracle provides a tool for formatting trace files-TKPROF (Transient Kernel Profiler), a tool that transforms SQL files into a format that analysts can easily understand.

A simple way to use a general tkprof tool is to use only two keywords: the trace file name and the output file name (Tkprof see additional information for details):
TKPROF <trace file> <output file>

Run in command-line mode (the database is installed under window2000)
C:>tkprof D:ORACLEADMINORA9IUDUMPORA9I_ORA_2060.TRC D:report.txt

After execution, the following statement is queried in Reprot.txt:
Select COUNT (*)
From
Sys_dept


Call count CPU Elapsed disk query current rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse 1 0.00 0.01 1 31 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.01 5 7 0 1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Total 3 0.00 0.03 6 38 0 1

Misses in library cache during parse:1
Optimizer Goal:choose
Parsing user id:62

By setting the TKPROF keyword [EXPLAIN = <username/password>], you can also add an execution plan for the SQL statement in the trace file:
C:>tkprof d:oracleadminora9iudumpora9i_ora_2060.trc d:report.txt explain=test/test;

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

Select COUNT (*)
From
Sys_dept


Call count CPU Elapsed disk query current rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse 2 0.00 0.01 1 31 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 5 14 0 2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Total 6 0.00 0.03 6 45 0 2

Misses in library cache during parse:1
Optimizer Goal:choose
Parsing user id:62

Rows Row Source operation
-------  ---------------------------------------------------
1 SORT AGGREGATE
TABLE ACCESS Full Sys_dept

Oracle SQL Trace Usage