Using DBMS_MONITOR

Using DBMS_MONITOR, Tracing has been enabled for diagnose and workload management based on, a specified client identifier or a hierarchical combination of service name, module name and action name. Also we have the facility to trace on session level.

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

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@ homedev> select serial#, sid , username ,terminal
  2  from v$session
  3  /

   SERIAL#        SID USERNAME                       TERMINAL
---------- ---------- ------------------------------ ----------------
     43513        124                                SRV2
      2341        131 HOME                           HOME-911726F9AC
     54375        136 HOME                           SRV2
     34813        138                                SRV2
     47466        140 SCOTT                          SRV1
      8044        146 HOME                           HOME-911726F9AC
         1        147                                SRV2
     12679        152 HOME                           MY-PC3
         1        154                                SRV2
     30986        156                                SRV2
     38634        159 HOME                           HOME-911726F9AC

  11 rows selected.

Elapsed: 00:00:00.10

Than you can start tracing with the command

SQL@ homedev> execute dbms_monitor.session_trace_enable(152,12679);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.35

A file will be created in user_dump_dest upon running above method.

SQL@ homedev>  

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

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@ homedev> execute dbms_session.set_identifier('Bukhari');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.85
SQL@ homedev> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;

CLIENT_ID
--------------------------------------------------------------------------------
Bukhari

SQL@ homedev> select client_identifier client_id from v$session where sid =152;

CLIENT_ID
----------------------------------------------------------------
Bukhari

Now we start the tracing for the client identifier 'Bukhari' with the function
SQL@ homedev> execute dbms_monitor.client_id_trace_enable('Bukhari');

PL/SQL procedure successfully completed.


  1  select primary_id, QUALIFIER_ID1, waits, binds
  2  from DBA_ENABLED_TRACES
  3* where trace_type = 'CLIENT_ID'
SQL@ homedev> /

PRIMARY_ID
----------------------------------------------------------------
QUALIFIER_ID1                                    WAITS BINDS
------------------------------------------------ ----- -----
Bukhari
                                                 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 ('Bukhari');
for the client identifier 'Bukhari'.
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.

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=

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

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 );

Examples:

The following is an example of a PL/SQL block that sets the module name 
   and action name:

CREATE Or REPLACE PROCEDURE Add_Employee( name       VARCHAR2(20),
                                          salary     NUMBER(7,2),
                                          manager    NUMBER,
                                          title      VARCHAR2(9),
                                          commission NUMBER(7,2),
                                          dept       NUMBER(2))
AS
BEGIN
        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,
                deptno);

        DBMS_APPLICATION_INFO.SET_MODULE ('','');
END;

CREATE Or REPLACE PROCEDURE  bal_tran (amt IN NUMBER(7,2)) AS
BEGIN

     DBMS_APPLICATION_INFO.SET_ACTION(action_name =>'transfer from chk to sav');

     UPDATE chk
     SET bal = bal + :amt
     WHERE acct# = :acct;

     UPDATE sav
     SET bal = bal - :amt
     WHERE acct# = :acct;

     COMMIT;
     DBMS_APPLICATION_INFO.SET_ACTION('');
END;