Create trace using DBMS_MONITOR

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

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

Exemple

set the identifier in the session you want to trace:

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

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

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

Don’t forget to disable the trace when done :

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

Exemple

To activate the trace:

To see if the trace is activated :

Then disable the trace when done :

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

Exemple

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

to see if trace is enabled :

to disable it :

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

Example

Activate the trace :

to disable the trace :

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

Example

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

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

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

Using Application Tracing Tools

I hope this article helped.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.