1. Viewing Enabled Traces
You can query DBA_ENABLED_TRACES to determine what traces are enabled.

Example:
SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds
             from DBA_ENABLED_TRACES;

TRACE_TYPE                   PRIMARY_ID  QUALIFIER_ID1     WAITS    BINDS
----------------------           ---------------   ------------------      --------    -------
SERVICE_MODULE          SYS$USERS           SQL*Plus            TRUE     FALSE
CLIENT_ID                         HUGO                                                 TRUE     FALSE
SERVICE                             v101_DGB                                          TRUE      FALSE

At this database we have three different trace state.
1.) The first row shows that we trace on the server all SQL statements that are executed in SQL*Plus.
2.) We trace all session that are used in a session with the client identifier 'HUGO'
3.) We trace all program that are connected to the database via the service 'v101_DGB'.

In the next section we explain how we enable and disable tracing.


2. Function session_trace_enable
You can use the SESSION_TRACE_ENABLE procedure to enable SQL tracing for a given database session on the local instance.

Syntax
You enable tracing with
dbms_monitor.session_trace_enable(session_id => x, serial_num => y,
waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );
and disable tracing with
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);
The default of waits is TRUE and the default of binds is FALSE.

Example
To get the session and the serial numbers you can query V$SESSION.

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

SERIAL#     SID   USERNAME
-------          -----  --------------
  1                 131
18                 139
  3                 140
11                143     SCOTT

Than you can start tracing with the command
SQL> execute dbms_monitor.session_trace_enable(143,11);

This tracing state is not persistent across a database shutdown and you see no entry in DBA_ENABLED_TRACES.

SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds
            from DBA_ENABLED_TRACES;

no rows selected

As disconnect from the session or the following command stop tracing
SQL> execute dbms_monitor.session_trace_disable(143,11);


3. Function client_id_trace_enable
In multitier environments, a request from an end client is routed to different database sessions by the middle tier. This means that the association
between the end client and the database session is non-static. Prior to version 10g, there was no easy way to keep track of a client across different database sessions. End-to-end tracing makes this possible by introducing a new attribute, CLIENT_IDENTIFIER, which is uniquely identifies a given end client. The client identifier is visible in the CLIENT_IDENTIFIER column of V$SESSION. It is also visible through the system context.

Syntax
You start tracing with
execute dbms_monitor.client_id_trace_enable ( client_id =>'client x',
waits => (TRUE|FALSE), binds => (TRUE|FALSE) );
and stop tracing with
execute dbms_monitor.client_id_trace_disable ( client_id =>'client x');
The default of waits is TRUE and the default of binds is FALSE.

Example
You can set the CLIENT_IDENTIFIER with the function SET_IDENTIFIER of the function DBMS_SESSION.
An example
SQL> execute dbms_session.set_identifier('HUGO');
Has described above we have 2 ways to see what CLIENT_IDENTIFIER we have set
SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;

CLIENT_ID
--------------
HUGO

SQL> select client_identifier client_id from v$session where sid = 142;

CLIENT_ID
--------------
HUGO

Now we start the tracing for the client identifier 'HUGO' with the function
SQL>execute dbms_monitor.client_id_trace_enable ('HUGO');
To check that the tracing is enabled use the query
SQL> select primary_id, QUALIFIER_ID1, waits, binds
     from DBA_ENABLED_TRACES
     where trace_type = 'CLIENT_ID';

PRIMARY_ID   QUALIFIER_ID1 WAITS BINDS
----------------   --------------        -------- --------
HUGO                                              TRUE  FALSE

This tracing is persistent across a database shutdown. You have to call a function to disable tracing. This can be done via the execution of
SQL>execute dbms_monitor.client_id_trace_disable ('HUGO');
for the client identifier 'HUGO'.
This would produce sometimes more than one trace file. As example when you use MTS, different shared server can execute the SQL statements. This will cause more than one trace file. The same can be true for RAC environments. We discuss in a later section how to combine this trace files with the utility trcsess to one file.


4. Function serv_mod_act_trace_enable
End-to-end tracing is also useful for efficient management and accounting of workload for applications using SERVICES that have been instrumented with MODULE and ACTION name annotation. Service name, module and action name provide a means to set apart important transactions in an application.
You can use the SERV_ACT_TRACE_ENABLE procedure to enable SQL tracing for a given combination of service name, module name and action name globally, unless an instance name is specified.
The service name , module name for a session is visible in the SERVICE_NAME and MODULE columns in V$SESSION.

Syntax
You start tracing with
execute dbms_monitor.serv_mod_act_trace_enable('Service S', 'Module M', 'Action A',
waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' );
and stop tracing with
execute dbms_monitor.serv_mod_act_trace_disable('Service S', 'Module M', 'Action A');
The default of waits is TRUE and the default of binds is FALSE. The default of instance_name is null.

Example
We would like to trace all SQL statement execute via the program SQL Plus on the database server.
We know that the session 142 is such a session.

SQL> select module, service_name from v$session where sid = 142;

MODULE    SERVICE_NAME
------------- ---------------------
SQL*Plus     SYS$USERS

SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' );

We can check that we tracing is enabled with the command

SQL> select primary_id, QUALIFIER_ID1, waits, binds
           from  DBA_ENABLED_TRACES
           where trace_type = 'SERVICE_MODULE';

PRIMARY_ID QUALIFIER_ID1 WAITS BINDS
---------------  ------------------- -------- --------
SYS$USERS   SQL*Plus              TRUE   FALSE

SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus' );


5. Combine trace files with trcsess
You get from some trace operation multiple trace files. In version prior to 10g you have manual put this trace file together. Now you have the utility trcsess that helps you to combine this trace files. You can say if you only want to do it for certain session or client identifiers.

Syntax
trcsess [output=<output file name >] [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>


6. The Package DBMS_APPLICATION_INFO
You can call the DBMS_APPLICATION_INFO SET procedure before beginning a transaction to register and name a transaction/client_info/module for later use when measuring  performance across an application. You should specify what type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources.


For this purpose DBMS_APPLICATION_INFO has the functions
SET_CLIENT_INFO ( client_info IN VARCHAR2 );
SET_ACTION ( action_name IN VARCHAR2 );
SET_MODULE ( module_name IN VARCHAR2, action_name IN VARCHAR2 );
to set the names.

Example
DBMS_APPLICATION_INFO.SET_MODULE ( module_name => 'add_employee',action_name => 'insert into emp');
INSERT INTO emp ( ename, empno, sal, mgr, job, hiredate, comm, deptno )
VALUES ( name, emp_seq.nextval, salary, manager, title, SYSDATE, commission, department);
DBMS_APPLICATION_INFO.SET_MODULE(null,null);


The following sample query illustrates the use of the MODULE and ACTION column of the V$SQLAREA.

SELECT sql_text
FROM    v$sqlarea
WHERE module = 'add_employee';

SQL_TEXT           
-----------------------------------------------------
INSERT INTO emp 1 add_employee insert into emp
(ename, empno, sal, mgr, job, hiredate, comm, deptno)
VALUES
(name, next.emp_seq, manager, title, SYSDATE, commission, department)


You can also read the information via the functions

DBMS_APPLICATION_INFO.READ_CLIENT_INFO ( client_info OUT VARCHAR2 );
DBMS_APPLICATION_INFO.READ_MODULE ( module_name OUT VARCHAR2, action_name OUT VARCHAR2 );

1. Viewing Enabled Traces
You can query DBA_ENABLED_TRACES to determine what traces are enabled.

Example:
SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds
             from DBA_ENABLED_TRACES;

TRACE_TYPE                   PRIMARY_ID  QUALIFIER_ID1     WAITS    BINDS
----------------------           ---------------   ------------------      --------    -------
SERVICE_MODULE          SYS$USERS           SQL*Plus            TRUE     FALSE
CLIENT_ID                         HUGO                                                 TRUE     FALSE
SERVICE                             v101_DGB                                          TRUE      FALSE

At this database we have three different trace state.
1.) The first row shows that we trace on the server all SQL statements that are executed in SQL*Plus.
2.) We trace all session that are used in a session with the client identifier 'HUGO'
3.) We trace all program that are connected to the database via the service 'v101_DGB'.

In the next section we explain how we enable and disable tracing.


2. Function session_trace_enable
You can use the SESSION_TRACE_ENABLE procedure to enable SQL tracing for a given database session on the local instance.

Syntax
You enable tracing with
dbms_monitor.session_trace_enable(session_id => x, serial_num => y,
waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );
and disable tracing with
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);
The default of waits is TRUE and the default of binds is FALSE.

Example
To get the session and the serial numbers you can query V$SESSION.

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

SERIAL#     SID   USERNAME
-------          -----  --------------
  1                 131
18                 139
  3                 140
11                143     SCOTT

Than you can start tracing with the command
SQL> execute dbms_monitor.session_trace_enable(143,11);

This tracing state is not persistent across a database shutdown and you see no entry in DBA_ENABLED_TRACES.

SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds
            from DBA_ENABLED_TRACES;

no rows selected

As disconnect from the session or the following command stop tracing
SQL> execute dbms_monitor.session_trace_disable(143,11);


3. Function client_id_trace_enable
In multitier environments, a request from an end client is routed to different database sessions by the middle tier. This means that the association
between the end client and the database session is non-static. Prior to version 10g, there was no easy way to keep track of a client across different database sessions. End-to-end tracing makes this possible by introducing a new attribute, CLIENT_IDENTIFIER, which is uniquely identifies a given end client. The client identifier is visible in the CLIENT_IDENTIFIER column of V$SESSION. It is also visible through the system context.

Syntax
You start tracing with
execute dbms_monitor.client_id_trace_enable ( client_id =>'client x',
waits => (TRUE|FALSE), binds => (TRUE|FALSE) );
and stop tracing with
execute dbms_monitor.client_id_trace_disable ( client_id =>'client x');
The default of waits is TRUE and the default of binds is FALSE.

Example
You can set the CLIENT_IDENTIFIER with the function SET_IDENTIFIER of the function DBMS_SESSION.
An example
SQL> execute dbms_session.set_identifier('HUGO');
Has described above we have 2 ways to see what CLIENT_IDENTIFIER we have set
SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;

CLIENT_ID
--------------
HUGO

SQL> select client_identifier client_id from v$session where sid = 142;

CLIENT_ID
--------------
HUGO

Now we start the tracing for the client identifier 'HUGO' with the function
SQL>execute dbms_monitor.client_id_trace_enable ('HUGO');
To check that the tracing is enabled use the query
SQL> select primary_id, QUALIFIER_ID1, waits, binds
     from DBA_ENABLED_TRACES
     where trace_type = 'CLIENT_ID';

PRIMARY_ID   QUALIFIER_ID1 WAITS BINDS
----------------   --------------        -------- --------
HUGO                                              TRUE  FALSE

This tracing is persistent across a database shutdown. You have to call a function to disable tracing. This can be done via the execution of
SQL>execute dbms_monitor.client_id_trace_disable ('HUGO');
for the client identifier 'HUGO'.
This would produce sometimes more than one trace file. As example when you use MTS, different shared server can execute the SQL statements. This will cause more than one trace file. The same can be true for RAC environments. We discuss in a later section how to combine this trace files with the utility trcsess to one file.


4. Function serv_mod_act_trace_enable
End-to-end tracing is also useful for efficient management and accounting of workload for applications using SERVICES that have been instrumented with MODULE and ACTION name annotation. Service name, module and action name provide a means to set apart important transactions in an application.
You can use the SERV_ACT_TRACE_ENABLE procedure to enable SQL tracing for a given combination of service name, module name and action name globally, unless an instance name is specified.
The service name , module name for a session is visible in the SERVICE_NAME and MODULE columns in V$SESSION.

Syntax
You start tracing with
execute dbms_monitor.serv_mod_act_trace_enable('Service S', 'Module M', 'Action A',
waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' );
and stop tracing with
execute dbms_monitor.serv_mod_act_trace_disable('Service S', 'Module M', 'Action A');
The default of waits is TRUE and the default of binds is FALSE. The default of instance_name is null.

Example
We would like to trace all SQL statement execute via the program SQL Plus on the database server.
We know that the session 142 is such a session.

SQL> select module, service_name from v$session where sid = 142;

MODULE    SERVICE_NAME
------------- ---------------------
SQL*Plus     SYS$USERS

SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' );

We can check that we tracing is enabled with the command

SQL> select primary_id, QUALIFIER_ID1, waits, binds
           from  DBA_ENABLED_TRACES
           where trace_type = 'SERVICE_MODULE';

PRIMARY_ID QUALIFIER_ID1 WAITS BINDS
---------------  ------------------- -------- --------
SYS$USERS   SQL*Plus              TRUE   FALSE

SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus' );


5. Combine trace files with trcsess
You get from some trace operation multiple trace files. In version prior to 10g you have manual put this trace file together. Now you have the utility trcsess that helps you to combine this trace files. You can say if you only want to do it for certain session or client identifiers.

Syntax
trcsess [output=<output file name >] [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>


6. The Package DBMS_APPLICATION_INFO
You can call the DBMS_APPLICATION_INFO SET procedure before beginning a transaction to register and name a transaction/client_info/module for later use when measuring  performance across an application. You should specify what type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources.


For this purpose DBMS_APPLICATION_INFO has the functions
SET_CLIENT_INFO ( client_info IN VARCHAR2 );
SET_ACTION ( action_name IN VARCHAR2 );
SET_MODULE ( module_name IN VARCHAR2, action_name IN VARCHAR2 );
to set the names.

Example
DBMS_APPLICATION_INFO.SET_MODULE ( module_name => 'add_employee',action_name => 'insert into emp');
INSERT INTO emp ( ename, empno, sal, mgr, job, hiredate, comm, deptno )
VALUES ( name, emp_seq.nextval, salary, manager, title, SYSDATE, commission, department);
DBMS_APPLICATION_INFO.SET_MODULE(null,null);


The following sample query illustrates the use of the MODULE and ACTION column of the V$SQLAREA.

SELECT sql_text
FROM    v$sqlarea
WHERE module = 'add_employee';

SQL_TEXT           
-----------------------------------------------------
INSERT INTO emp 1 add_employee insert into emp
(ename, empno, sal, mgr, job, hiredate, comm, deptno)
VALUES
(name, next.emp_seq, manager, title, SYSDATE, commission, department)


You can also read the information via the functions

DBMS_APPLICATION_INFO.READ_CLIENT_INFO ( client_info OUT VARCHAR2 );
DBMS_APPLICATION_INFO.READ_MODULE ( module_name OUT VARCHAR2, action_name OUT VARCHAR2 );