Oracle Tracing SQL Code by using SQL Trace and TKProf

If you are running any query in oracle and you want to know what is running in background then the simplest way is to trace the session. On tracing of session, trace file is created that Oracle Database creates for each and every event running in background.  These trace files are more useful when you want to diagnose any error or debug the database.

How to enable session level tracing?

We have many ways to enable the session level tracing. You can use any of the following tracing method as per your requirement.

1) Tracing all database session:

It can be enabled by modifying SQL_TRACE parameter in database parameter file or by using following command:

ALTER SYSTEM SET SQL_TRACE=TRUE;  --Query to Enable SQL_TRACE
ALTER SYSTEM SET SQL_TRACE=FALSE;  --Query to Disable SQL_TRACE

2) Tracing own session:

You can trace your own session by using following command:

ALTER SESSION SET SQL_TRACE=TRUE;

Oracle also provides option to trace through 10046 event. This event gives additional level details in trace file.  This can be set by using following command

ALTER SESSION set events '10046 trace name context forever, level 8';

To turn off the tracing following command can be used:

ALTER SESSION set events '10046 trace name context off';

Here, we have used ‘context forever’ clause so that we can trace the session until it ends.  Also, we have used ‘level 8’ which will give more detail about “Wait Information” that can help in determining the poor performance.

We can use following levels of tracing for setting any event

Level   Description
------  --------------------
1       Normal Trace File
4       Dump bind variable values
8       Dump wait information
12      Dump all information

3) Tracing others session:

To trace other session you need SID, SERIAL# of user than can be fetched using following query:

SELECT 
      SID,
      SERIAL#,
      USERNAME,
      OSUSER,
      MACHINE
FROM 
      V$SESSION
WHERE 
      USERNAME IS NOT NULL;

Output:

SID   SERIAL#  USERNAME  OSUSER      MACHINE
----  -------- --------- ----------- ---------------- 
16    23       ATUL      PANKAJ\atul WORKGROUP\PANKAJ
125   169      ATUL      PANKAJ\atul WORKGROUP\PANKAJ
144   151      SYS       PANKAJ\atul WORKGROUP\PANKAJ

Now, we will use following statement to trace session for SID 16

Exec sys.dbms_system.set_ev(16,23,10046,12,' ');

In above statement, value ’12’ used in fourth input parameter for setting level and last parameter value we have kept blank that is used for setting event name.

4) Tracing Parameters:

Following are some of the parameters used while tracing any session:

SELECT 
      NAME, 
      VALUE
 FROM 
      V$PARAMETER
 WHERE 
      UPPER(NAME) in
      ('USER_DUMP_DEST','TIMED_STATISTICS','MAX_DUMP_FILE_SIZE','SQL_TRACE');

NAME                VALUE
------------------- --------------------------------------
timed_statistics    TRUE
user_dump_dest      c:\app\atul\diag\rdbms\orcl\orcl\trace
sql_trace           FALSE
max_dump_file_size  unlimited

Parameter Details:
——————————–
timed_statistics       – This enables and disables the collection of timed statistics
user_dump_dest     –  Default trace directory where files are created.
sql_trace                    –   It enables database wide tracing.
max_dump_file_size – It sets the maximum trace file size.

5) Reading TKProf output:

Now, you have generated the trace file in folder location specified under user_dump_dest parameter which is in our case is “c:\app\atul\diag\rdbms\orcl\orcl\trace” as shown above.  Trace files always gets generated with ‘.trc’ extension. To read this file you have to use TKProf utility as shown below

tkrpof inputfile.trc output.txt

inputfile.trc — This will be your input trace file created in above steps.
output.txt    — This will be your output file name

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.