SQL Trace

This article lists the commands required to trace SQL statements executed by a user, an application or the entire database.

Tracing a SQL session

Start session trace

To start a SQL trace for the current session, execute:

ALTER SESSION SET sql_trace = true;

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

ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;

Stop session trace

To stop SQL tracing for the current session, execute:

ALTER SESSION SET sql_trace = false;

Tracing other user's sessions

DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to 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
total 8
-rw-r-----    1 oracle   dba         2764 Mar 30 12:37 ora_9294.trc

Tracing an entire database

To enable SQL tracing for the entire database, execute:

ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;

To stop, execute:

ALTER SYSTEM SET sql_trace = false SCOPE=MEMORY;

Identifying trace files

Trace output is written to the database's UDUMP directory.

The default name for a trace files is 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 by the parameter MAX_DUMP_FILE_SIZE. The unit of this parameter, if you don't specify the K or M option, is in OS block size.

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

On the other hand, when this parameter is set to UNLIMITED (default value), if the program to be traced is working forth and forth and the trace mode is not finished, the trace file can grow without limit which means until the associated file system or disk is full. A DBA can stop the trace of a session using the DBMS_MONITOR (10g and up), 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.

Also see

• TKProf, Oracle's utility for formatting SQL_TRACE output.

External links

• itrprof SQL Analyzer, web based tool which analysing SQL_TRACE and Event 10046 trace files.
• FlexTracer, commercial client-side SQL tracer for Oracle.
• QueryAdvisor, commercial GUI based client-side SQL tracefile analyzer for Oracle single instance and RAC.