Oracle SQL Tracing

Tracing SQL in Oracle Database 10g

Reference:
http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html


Introduction:
This extract briefs you about how to enable tracing at a session level in oracle 10g.

Getting Started:
As with prior Oracle database releases, trace files are output to the directory specified by the user_dump_dest parameter of the server's initialization file. You can dynamically change this parameter by using the alter system command

SQL> alter system set user_dump_dest="c:\kflosstrace";

You can also add your own marker to the trace file names so you can more easily find the generated files. To do so, set the tracefile_identifier initialization parameter before starting a trace:

SQL> alter session set tracefile_identifier ="kfloss_test";

Trace files generated by this command have the string value you set appended to the filenames.

Setting Up Tracing with DBMS_MONITOR:
To trace the SQL based on the session ID query the V$SESSION view

SQL> select sid, serial#, username from v$session;
SID SERIAL# USERNAME
------ ------- ------------
133 4152 SYS
137 2418 SYSMAN
139 53 KIMBERLY
. . .
170

128 rows selected.

With the session ID (SID) and serial number, you can use DBMS_MONITOR to enable tracing for just this session:

SQL> exec dbms_monitor.session_trace_enable(139);

The serial number defaults to the current serial number for the SID (unless otherwise specified), so if that's the session and serial number you want to trace, you need not look any further. Also, by default, WAITS are set to true and BINDS to false, so the syntax above is effectively the same as the following:

SQL> exec dbms_monitor.session_trace_enable
(session_id=>139, serial_num=>53, waits=>true, binds=>false);

Analyzing Trace Results:
You can then run TKPROF against the consolidated trace file to generate a report.

C:\...\udump> tkprof kfloss.trc
output=kfloss_trace_report SORT=(EXEELA, PRSELA, FCHELA)

If you don't disable tracing, every session that runs that service and module will be traced. Thus, when you're finished, be sure to disable tracing by using either Oracle Enterprise Manager or the DBMS_MONITOR package.