Session Tracing in Oracle 10G

With the advent of Oracle 10g, the SQL tracing options have been
centralized and extended using the dbms_monitor package.
The example below shows how we can trace the sessions for a particular
schema/user without generating unwanted or junk trace files.

STEP 1
------
Before starting the trace, we need to identify the schema/user which
needs to be traced. For this, we shall create a logon trigger which would
attach a particular CLIENT_IDENTIFIER to the session logging into the
schema.

CREATE OR REPLACE TRIGGER "SCOTT".LOGON_TRIGGER
AFTER LOGON ON schema
DECLARE
v_user_identifier varchar2(64);
BEGIN
v_user_identifier:='SCOTT_ID';
DBMS_SESSION.SET_IDENTIFIER(v_user_identifier);
END;

All the sessions connecting to the SCOTT schema will be have the
CLIENT_IDENTIFIER set to "SCOTT_ID". This enables the database to identify all
the sessions connected to a schema, with just one name/id.

STEP 2
------
Now we shall enable the tracing for the attached client id using
following command:

EXEC
DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('SCOTT_ID',WAITS=>TRUE,BINDS=>TRUE);

This will start tracing for all the sessions that has SCOTT_ID as
CLIENT_ID.
The trace files will be generated in the directory specified in the
value of parameter "USER_DUMP_DEST".
Mostly it is set to UDUMP directory.

STEP 3
------
Tracing can be disabled using the following command

EXEC DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('SCOTT_ID');

STEP 4
------
Activating trace on multiple sessions means that trace information is
spread throughout many trace files. For this reason Oracle 10g
introduced the trcsess utility, allowing trace information from multiple trace
files to be identified and consolidated into a single trace file.
Go to the udump directory and execute the following command.

TRCSESS OUTPUT=OUTPUT.TRC CLIENTID=SCOTT_ID *
SYNTAX:
trcsess [output=

] [session=]
[clientid=] [service=] [action=]
[module=]
output=

output destination default being standard
output.
session= session to be traced.
Session id is a combination of session Index & session serial number
e.g. 8.13.
clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card
'*' supported.

This command will create with filename specified in parameter "OUTPUT"
i.e,. OUTPUT.TRC in this case.

STEP 5
------
We have a consolidated tracefile OUTPUT.TRC which contains traces for
all the sessions that had connected to SCOTT schema. We must now use
TKPROF utility to create a human readable file from this raw trace file.

TKPROF OUTPUT.TRC SCOTT_SESSIONS.TXT

STEP 6
------
The human readable form of the tracefile can be used to identify
problems with the application, database, etc.
There is a thrid party tool available (developed by Siva Dirisala)
which analyzes the SCOTT_SESSIONS.TXT file query wise.

http://www.dirisala.net/oracle/tkprof_trace_viewer.do

For detailed descriptions please refer to the Oracle documentation
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#sthref1994

"Happy Tracing"

Aalap Sharma