Different methods to trace a session in oracle

Sometimes a user may be running an application in which hundreds of queries are executed. In such scenarios, it may be hard to pinpoint exactly which SQL statement is causing performance issues can turn on SQL tracing to capture statistics regarding all SQL statements run by a user. Through trace you can instruct Oracle to capture resources consumed and execution plans for all SQL statements run by a session.

Here are the general steps to take to trace a session:

1. Enable tracing.

2. Run the SQL statements you want traced.

3. Disable tracing.

4. Use a utility such as tkprof, trcsess, or the Oracle Trace Analyzer to translate

the trace file into a human-readable format

Oracle provides a wide variety of methods to generate a SQL resource usage trace file (quite frankly, to the point of being confusing), including:

• DBMS_SESSION
• DBMS_MONITOR ( available from 10g onwards and is recommended )
• DBMS_SYSTEM
• DBMS_SUPPORT  (not installed by default)
• ALTER SESSION
• ALTER SYSTEM
• oradebug  (commonly used in oracle 8i and older version)

The method you use depends on your personal preferences and various aspects of your

environment, such as the version of the database and PL/SQL packages installed. Each of these tracing methods is described briefly in the following subsections.

Trace levels
Trace levels can be used to define the type of information in the trace file.  There are a number of different trace levels, which generate different logging information.
0 - trace is off
1 – standard SQL (execute-fetch-parse) trace no, no wait events, or bind variables. level 1 is the default trace level.
2.same as level 1 and not commonly used
4 – Bind variables only
8 – Wait events only
12 – Bind Variables and Wait Events

Init.ora/spfile parameters

There are a few init.ora parameters that need to be set for the sessions you want to trace.  They are as follows:
timed_statistics=true
statistics_level=typical|all
This is only really required for the waits or sql timing information.  Should be enabled by default on most system. 
max_dump_file_size=unlimited
This allows the trace file to grow as required by the trace.  If this is not set to unlimited, then you may find your trace file does not cover all activity, and you will see a message similar to “*** DUMP FILE IS LIMITED TO xxxxx BYTES ***” at the end of your trace file..

11g R1 and above: 
With the introduction of the new diagnosability infrastructure introduced in Oracle Database 11g Release 1, traces and core files are placed in a location controlled by the DIAGNOSTIC_DEST initialization parameter.
To show the location of the DIAGNOSTIC_DEST, the following command can be used:   

SQL> show parameter diagnostic_dest

Oracle allows you to set the TRACEFILE_IDENTIFIER parameter at session level, allowing you to include some recognizable text into the trace file name.

SQL>alter session set tracefile_identifier='TEST1';

The tracefile name will be something like <instance><_ora_<spid>.trc.
in case if you give any tracefie identifier like alter session set tracefile_identifier='TEST1'; then trace file will be something like  <instance><_ora_<spid>_TEST1.trc.

Using DBMS_SESSION  (Available from oracle 7.3.4)

Using the DBMS_SESSION package was covered in the solution section of this recipe. Here’s a brief summary of how to enable and disable tracing using DBMS_SESSION:
SQL> exec dbms_session.set_sql_trace(sql_trace=>true);
SQL> -- run sql statements that you want to trace...
SQL> exec dbms_session.set_sql_trace(sql_trace=>false);

Using DBMS_SYSTEM  (Available from oracle 7.3.4)

To enable SQL tracing in another session, you can use the DBMS_SYSTEM package. You first must identify the session you want to trace:

SQL> select username,sid,serial# from v$session;

Pass the appropriate values to the following lines of code:

SQL> exec dbms_system.set_sql_trace_in_session(sid=>200,serial#=>5, sql_trace=>true);

Run the following to disable tracing for the session:

SQL> exec dbms_system.set_sql_trace_in_session(sid=>200,serial#=>5, sql_trace=>false);

You can also use DBMS_SYSTEM to capture wait events:

SQL> exec dbms_system.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');

SQL> exec dbms_system.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');
where si=sid , se=serial# , ev=events , le=level and nm= name.

Using DBMS_SUPPORT  (Available from oracle 8.0.6)

This technique requires that you first load the DBMS_SUPPORT package (it’s not created by default):
SQL> @?/rdbms/admin/dbmssupp.sql
Use the following syntax to enable and disable tracing in your current session:
SQL> exec dbms_support.start_trace(waits=>TRUE, binds=>TRUE);
SQL> -- run sql statements that you want to trace...
SQL> exec dbms_support.stop_trace;
Use this syntax to enable and disable tracing in session other than your own:
SQL> exec dbms_support.start_trace_in_session(sid=>123, serial=>1234,-
> waits=>TRUE, binds=>TRUE);
SQL> exec dbms_support.stop_trace_in_session(sid=>123, serial=>1234);

Using DBMS_MONITOR (Available from oracle 10.1)

If you are using Oracle Database 10g or higher, it is  recommend to use DBMS_MONITOR package, which offers a high degree of flexibility, for facilitating tracing. To enable and disable tracing within the current session use the following statements:

SQL> exec dbms_monitor.session_trace_enable;

SQL> -- run sql statements that you want to trace...

SQL> exec dbms_monitor.session_trace_disable;

Use the WAIT and BINDS parameters to enable tracing with wait and bind variable information:

SQL> exec dbms_monitor.session_trace_enable(waits=>TRUE, binds=>TRUE);

Note:- Wait events track the amount of time spent waiting for a resource. Bind variables are substitution variables used in place of literal variables.

Use the SESSION_ID and SERIAL_NUM parameters to enable and disable tracing for an already connected session. First run this SQL query to determine the SESSION_ID and SERIAL_NUM for target session:

Use the following query 
column line format a88
select 'ospid: ' || p.spid || ' # ''' ||s.sid||','||s.serial#||''' '||
s.osuser || ' ' ||s.machine ||' '||s.username ||' '||s.program line
from v$session s , v$process p
where p.addr = s.paddr
and s.username ='&USER'and s.status='ACTIVE' and s.type='USER'; 
 

Now use the appropriate values when calling DBMS_MONITOR

SQL> exec dbms_monitor.session_trace_enable(session_id=>1234, serial_num=>12345);

To stop the session level tracing .

SQL> exec dbms_monitor.session_trace_disable(session_id=>1234, serial_num=>12345);

You can also enable the tracing of wait and bind variable information as follows:

SQL> exec dbms_monitor.session_trace_enable(session_id=>1234, serial_num=>12345, waits=>TRUE, binds=>TRUE);

check: 
SELECT sql_trace,sql_trace_waits,sql_trace_binds FROM v$session;
for trace enbaled session

From 11g onwards to identify the trace file execute the following query 
SELECT p.tracefile FROM   v$session s JOIN v$process p ON s.paddr = p.addr WHERE  s.sid = &sid
 You can also specify client_id in dbms_monitor,
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall');
SQL> EXEC DBMS_MONITOR.client_id_trace_enable(client_id=>'tim_hall', waits=>TRUE, binds=>FALSE);
SQL> EXEC DBMS_MONITOR.client_id_trace_disable(client_id=>'tim_hall');
You can turn on trace at database level,
SQL>exec DBMS_MONITOR.DATABASE_TRACE_ENABLE (waits => TRUE,binds => TRUE,instance_name > NULL);
turn off
SQL>exec DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name > NULL);
check:
SQL>SELECT * FROM dba_enabled_traces;

Altering Your Session

You can use ALTER SESSION to turn tracing on and off:

SQL> alter session set sql_trace=true;

SQL> -- run sql commands...

SQL> alter session set sql_trace=false;

Note:-  The SQL_TRACE parameter is deprecated. Oracle recommends that you use the DBMS_MONITOR or DBMS_SESSION packages to enable tracing.

Altering the System

You can turn on tracing for all sessions in the database by using this ALTER SYSTEM statement:

SQL> alter system set sql_trace=true;

Use the following SQL to disable system-wide tracing:

SQL> alter system set sql_trace=false;

Caution:-  it is not  recommend to set SQL tracing at the system level as it can severely degrade system performance.

Using oradebug

The oradebug utility can be used to enable and disable tracing for a session. You need SYSDBA privileges to run this utility.

You can identify a process to trace either by its operating system process ID or its Oracle process ID.

To determine these IDs, run the following SQL query for the username you want to trace (in this example the user is mahi ):

SQL> select spid os_pid, pid ora_pid from v$process

where addr=(select paddr from v$session where username='MAHI');

Here is the output for this example:

OS_PID ORA_PID

------------------------ ----------

8828 17

Next, use oradebug with either the SETOSPID or SETORAPID option to attach oradebug to a session. This example uses the SETOSPID option:

SQL> oradebug setospid 8828; - here we started tracing on os process id 8828

Now you can set tracing on for the session:

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 8;

You can verify that tracing is enabled by viewing the trace file name:

SQL> oradebug TRACEFILE_NAME;

Use the following syntax to disable tracing:

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT OFF;

Use the HELP option to view all features available in the oradebug utility:

SQL> oradebug help

Diagnostic events

when insufficient information is available to resolve a given problem we can use oracle
diagnostic events to produce additional diagnostic information.The Oracle database kernel is instrumented with over four hundred so-called “pseudoerror debugging events.”
Most of these events are primarily used to produce additional diagnostic inforamation to change Oracle's behaviour and to trace the inner working of oracle because these events can enable some of the undocumeted features of oracle. For example most commonly used diagnostic events are 10046 , 10053 etc.Oracle diagnostic events are classified into 4 major categories based on their usage,

Immediate dump events
immediate dump event will dump the diagnostic information in a trace file as soon as the command is fired. Normally immediate dump are used to dump the system state , process state, and file headers (controlf , redo_hdrs,file_hdrs) .It can't invoked by setting EVENT parameter in the init file. It can be triggered by alter session set events command or by using oradebug command.
 SQL> alter session set events 'immediate trace name systemstate level 8';
 The preceding command creates a trace file , which contains detailed information
about every oracle process active on the system, as well as the details about the
resources held/request by those process. Another example of an immediate dump event is
 SQL>alter session set events 'immediate trace name controlf level 10';

On error dump events
On error dump events  are similar to immediate dump events but they are invoked only
when the error occurs. They are not used to dump the system state , prcoess state or
error stack when a particular oracle error occures .Normally set using the EVENT parameter in the INIT.ora file. Following example shows an entry in the init.ora file to dump the error stack to trace file when ORA-04020 (Dead lock detected when waiting for an object ) is encountered.
event= "4020 trace name errorstack level 1"
On error dumps events are most commonly used to identify the cause of a particular error, and the oracle support may ask you to dump the error stack for further investigation. For example , to diagnose the shared memory leak (ORA- 04031) will need the following settings at init.ora file
event = " 4031 trace name errorstack level 10"
Usaully on error  dumps level set between 1 and 10.

Change behaviour events
change behaviour events are set using EVENT parameter in the init.ora file . These events are very powerful events and should be used with care.These events are used to enable or disable certain functionalitied of the oracle kernel.Unlike other diagnostic events these events do not have level numbers because these event do not create a trace file.
Change behaviour events have additional keyword : forever, without these keyword
the event id fired only once and no subsequent actions are triggered. Change behaviour events uses a reserverd set of numbers to bring about the change in oracle behaviour.
for example following event disables the automatic shrinking of rollback segment by smon process. which is usually happnes every 12 hrs .
event = " 10512 trace name context forever"
Many of the change behaviour events are potentially dangerous to the database as they can change the default behaviour of database .

Process trace events
proces strace events are used to get additional information when the process is running .These events are normally harmless , and they do generate a trace file. Following are the valid example for setting two events,
event="4031 trace name context forever, level 10"
evnet="10046 trace name context forever, level 12"
or you can
event="4031 trace name context forever, level 10: 10046 trace name context forever, level 12"or by
SQL>alter session set events '10046 trace name context forever, level 12';

we  came to know that  what these events does, it enables extended SQL trace. But why such difficult syntax?
This syntax actually reveals some of the power of KSD diagnostic event syntax (KSD=kernel service debug):
10046
The first word in event string (the 10046) specifies the when should some action should be taken. 10046 events enable sql statement timing ,and everything after that (trace name context forever, level 12) is that action.
trace
The first word in action (trace) specifies what type of action Oracle needs to take if that event (10046) occurs. In event syntax, “trace” is most generic action, which is used for tracing, behavior changing and Oracle dumps.
name
The “name” specifies that the name of what to dump/trace will follow. The “name” is always present when “trace” option is used (as far as I know).
context
Now the next keyword (context) is the one where you can define whether you want Oracle to dump something when the event is hit or just do context specific trace. If you replace the “context” with “errorstack” for example, you wouldn’t get SQL trace output, but rather an Oracle errorstack dump whenever event 10046 is hit.You can use “oradebug dumplist” to find all the possible dump commands what you can set as actions for an event.
forever
The next keyword (forever) is actually an option to the action, not an action keyword itself. Forever means that keep invoking the action when the 10046 event is hit, forever (or until explicitly disabled). If we don’t specify forever, then the action would be invoked only once and the event will disable itself after that.
level 12
The “level 12? is also just another option to the action, specifying that the value for that event handler in given session should be 12. This means that whenever some Oracle function is checking whether that event is set, they will be returned value 12, the calling function interprets the value and acts appropriately (traces both binds and waits in our case).

Note:-
1. you can also set event using oracle supplied package procedure called  DBMS_SYSTEM.SET_EV
2. alter session set sql_trace = true; which in functionality is equivalent to the
alter session set events '10046 trace name context forever, level 1'; and dbms_session.set_sql_trace (true);