Tracing Sessions: Single SQL_ID, Single Process, New 11g Event Syntax

Happy Reading!!!

Hope you already aware of tracing sessions with different methods

11g onwards, you can trace a session (other session) with in the alter system command itself , no worries of finding pid etc etc. for example like below

SQL> alter session set events 'sql_trace {process : pid = <pid>, pname = <pname>, orapid = <orapid>} rest of event specification'

pid: v$process pid

pname: name of process

orapid: V$process spid

Reference Note:- Metalink (MOS) Doc ID 813737.1, “How To Use The New 11g Events++ Syntax For Easier SQL Tracing Of Datapump

Further you can trace single SQL ID, look below,

SQL> alter session set events ‘sql_trace [sql:abcdhehe2v9s1]’;

SQL> alter session set events ‘sql_trace [sql:abcdhehe2v9s1] off’;

I am sure this is very useful when we troubleshoot or want to trace particular Sql_id or the process ID.

Old Procedure;-

Before enable session statistics need to turn on timed statistics. so that the trace files get timing info and also set the dump file size so that there is plenty of room for the trace being generated.

exec dbms_system.set_bool_param_in_session(10,20,'timed_statistics',true);

exec dbms_system.set_int_param_in_session(10,20,'max_dump_file_size',size in bytes);

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 complexity 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.

To enable Session statistics in Oracle 10g/11g

EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);

To disable Session statistics in Oracle 10g/11g

EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

Tracing an entire database

To enable SQL tracing for the entire database, execute:

ALTER SYSTEM SET sql_trace = true SCOPE=MEMORY;

To disable SQL tracing for the entire database

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.

Use tkprof to format you trace file into a more readable format.

Syntax:

tkprof <source trace file> <output file>

Hope this helps.