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.

 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.