Description of the DBMS_MONITOR package

DBMS_MONITOR come for replacement of DBMS_SUPPORT with new functionnalities and easier management.
DBMS_MONITOR can be used to trace sessions or make traces with larger scope.
First let’s have a look to the available procedures of DBMS_MONITOR. Each procedure exists twice, one to enable the trace and one to disable it.

The CLIENT_ID_TRACE procedure

Description

This procedure is used to enable tracing using a Client Identifier.
This means that all sessions using  the same Client Identifier will be traced at the same time. This type of tracing is useful when you have to make a trace for an application using an application pool to connect to the database.
The trace will persist after a database restart and you’ll have to explicitly disable it.

Syntaxe

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(
 client_id    IN  VARCHAR2,
 waits        IN  BOOLEAN DEFAULT TRUE,
 binds        IN  BOOLEAN DEFAULT FALSE,
 plan_stat    IN  VARCHAR2 DEFAULT NULL);

Exemple

set the identifier in the session you want to trace:

SQL> exec DBMS_SESSION.SET_IDENTIFIER('this is a test');

PL/SQL procedure successfully completed.

Then enable the trace for the client identifier, execute the following command in another session:

SQL>  exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('this is a test',true,true);

PL/SQL procedure successfully completed.

To see if the trace is enabled, you can query the dba_enabled_trace view :

SQL> select TRACE_TYPE,PRIMARY_ID,WAITS,BINDS,PLAN_STATS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID                                                       WAITS BINDS PLAN_STATS
--------------------- ---------------------------------------------------------------- ----- ----- ----------
CLIENT_ID             this is a test                                                   TRUE  TRUE  FIRST_EXEC

You can choose to get the waits, binds ans plan_stats using the parameters.

Don’t forget to disable the trace when done :

SQL> exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('this is a test');

PL/SQL procedure successfully completed.

The DATABASE_TRACE procedure

Description

This procedure can be used to enable the trace for the whole database or for a specific instance in case RAC is used.

Syntaxe

DBMS_MONITOR.DATABASE_TRACE_ENABLE(
   waits          IN BOOLEAN DEFAULT TRUE,
   binds          IN BOOLEAN DEFAULT FALSE,
   instance_name  IN VARCHAR2 DEFAULT NULL,
   plan_stat      IN VARCHAR2 DEFAULT NULL);

Exemple

To activate the trace:

exec dbms_monitor.database_trace_enable(waits=>true,binds=>TRUE);

PL/SQL procedure successfully completed.

To see if the trace is activated :

SQL>  select TRACE_TYPE,PRIMARY_ID,WAITS,BINDS,PLAN_STATS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID                                                       WAITS BINDS PLAN_STATS
--------------------- ---------------------------------------------------------------- ----- ----- ----------
DATABASE                                                                               TRUE  TRUE  FIRST_EXEC

Then disable the trace when done :

SQL> exec dbms_monitor.database_trace_disable;

PL/SQL procedure successfully completed.

The SERV_MOD_ACT_TRACE procedure

Description

This procedure can be used to trace a particular application. It is possible to filter more finely application specifying the type of action you want to trace.

Syntaxe

dbms_monitor.serv_mod_act_trace_enable(
service_name  IN VARCHAR2,
module_name   IN VARCHAR2 DEFAULT ANY_MODULE,
action_name   IN VARCHAR2 DEFAULT ANY_ACTION,
waits         IN BOOLEAN  DEFAULT TRUE,
binds         IN BOOLEAN  DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL,
plan_stat     IN VARCHAR2 DEFAULT NULL);

Exemple

Consider you want to trace every user using SQL*PLUS, you can use this command :

exec dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus', dbms_monitor.all_actions, TRUE, TRUE);

to see if trace is enabled :

SQL> select TRACE_TYPE,PRIMARY_ID,QUALIFIER_ID1,QUALIFIER_ID2,WAITS,BINDS from dba_enabled_traces;

TRACE_TYPE            PRIMARY_ID                                                       QUALIFIER_ID1                                    QUALIFIER_ID2                    WAITS BINDS
--------------------- ---------------------------------------------------------------- ------------------------------------------------ -------------------------------- ----- -----
SERVICE_MODULE        SYS$USERS                                                        SQL*Plus                                                                          TRUE  TRUE

to disable it :

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

PL/SQL procedure successfully completed.

The SESSION_TRACE Procedure

Description

The SESSION_TRACE procedure is used to trace a specific session using the SID and SERIAL# columns of the v$session view. Like the other procedures you can use parameters to collect binds and waits.

Syntaxe

DBMS_MONITOR.SESSION_TRACE_ENABLE(
    session_id   IN  BINARY_INTEGER DEFAULT NULL,
    serial_num   IN  BINARY_INTEGER DEFAULT NULL,
    waits        IN  BOOLEAN DEFAULT TRUE,
    binds        IN  BOOLEAN DEFAULT FALSE,
    plan_stat    IN  VARCHAR2 DEFAULT NULL);

Example

Activate the trace :

SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(5,14,true,true);

PL/SQL procedure successfully completed.

to disable the trace :

SQL> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(5,14);

PL/SQL procedure successfully completed.

This trace will not be visible in the database_enabled_trace view and will not persist after a database restart.

Using TRCSESS to aggregate your trace files

When you use a client_id trace, or procedure which can generate more than one trace file you will have to aggregate the files in order to analyze them all at the same time. This is the purpose of the TRCSESS utility.

Syntaxe

trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]
         [action=action_name]
         [module=module_name]
         [trace_files]

Example

If I need to aggregate the traces I made for the client_id ‘this is a test’, I’ll use the following syntaxe

trcsess output=mytracefile.trc clientid='this is a test' *.trc

One my file mytracefile.trc has been generated I can use TKPROF utility to analyze it :

tkprof mytracefile.trc mytraceoutput.log

You can refer to this well explained oracle documentation to learn more about application tracing.

Using Application Tracing Tools

I hope this article helped.