Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Generating the SQL Trace File
Oracle Tips by Burleson

Once the sql_trace=true command is set with the alter session command, you will get a trace file for all SQL commands issued by your session. This trace file is known as a level-1 trace. You can also issue commands to cause Oracle to create a super-detailed level-4 trace file where additional detail is required.

Oracle will generate trace files for every session where the value of sql_trace=true and write them to the user_dump_dest destination. These file are stored in the form $ORACLE_SID_ora_nnn.trc in the trace directory, where nnn is a sequential number. Note that if you are not in the UNIX dba group, the generated files may not be accessible. If this is the case, you need to go to the DBA to change the UNIX permissions on the trace files before you can use TKPROF to format them. It is a good idea to note the date/time you create the trace file in case others are also creating trace files, since the identification is by a number in the filename.

Advanced SQL Trace File Generation

Oracle also provides a facility for getting a super-detailed trace file called a level-4 trace. This is generally only done at the request of Oracle Technical Support, but advanced SQL tuning professionals can sometimes find useful information in a level-4 trace file.

Generating a level-4 trace file requires that you know the SID and SERIAL# for the session to trace. You can use the following query to get the SID and SERIAL# for the session you wish to trace.


rem session.sql - displays all connected sessions
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
ttitle "dbname Database|UNIX/Oracle Sessions";

set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
b.paddr = a.addr
and type='USER'
order by spid;

spool off;

Here is a sample of the output from session.sql. Note that it shows the OS PID, the SID, the SERIAL#, and the originating client:

SQL> @session

Sun Apr 01                                                   page    1
                              UNIX/Oracle Sessions

----  --- ----- ------ ---------- -------- ------------------------------
7330   13 11967 sting  OPS$ORACLE oracle   sqlplus@sting (TNS V1-V3)
8214   10 1261  taz    READER     root     rdbqry_ora@taz (TNS V1-V3)

Once you know the SID, you can issue the following command to get a level-4 trace file for a user. In this example, we will trace the session for SID 10:

Connect system as sysdba;

You can also use the dbms_support package to generate a level-4 trace file. To use the dbms_support package, you must specify the SID and SERIAL#:

EXEC DBMS_SUPPORT.START_TRACE_IN_SESSION(10,1261,waits=>false, binds=>true)

The dbms_support package also has a 'stop' command to turn off the level-4 trace.


Remember, these trace files are internal dumps, and they will not be readable until they are formatted for visual display. This is where we use the TKPROF utility.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter