Oracle Session Tracing Part III

Explore how to enable and disable Oracle tracing with DBMS_MONITOR package in 10g.

  • In Part I, we learned how to set the CLIENT_IDENTIFIER session
    variable to more accurately pinpoint resource consumption for an individual
    user or group of users. We did this through queries to real-time activity views
    such as V$SESSION and V$ACTIVE_SESSION_HISTORY.

  • In Part II we learned how to set the ACTION and MODULE name so
    that we could track where in our code transactions where being executed and
    track the use of resources by transaction type or section of code.

Along with these possibly new session identifiers, there have
always been three session environment variables, set from when a user connects
to the database, that are important when talking about tracing a particular
session or group of sessions. These variables are the SID, SERIAL#, and
SERVICE_NAME. These variables have been around Oracle sessions for quite some
time and are presented in Table 1, along with the three new variables
presented in Parts I & II of this series, to provide for a quick refresher.
This set of six session variables now gives us the ability to activate tracing
for the session in a variety of ways.

Table 1


Session Variable


Description

SID

Identifier for the connected session.

SERIAL#

Serial number of the connected.

SERVICE_NAME

Service name of the session and represents the database
connected to.

MODULE

Name to represent the module or code being executed.

ACTION

Name to represent the action being performed by the
module.

CLIENT_IDENTIFIER

Identifier to represent the client session

DBMS_MONITOR Package

In Oracle 10g, we have been given the DBMS_MONITOR package.
This package allows us to interact and control the tracing and statistics
gathering of sessions through a PL/SQL interface. Table 2 gives all of
the programs we can call through the DBMS_MONITOR package. As you can see, our
six variables are represented for us to begin tracing through. Table 3
gives a quick reference guide to the available programs within the DBMS_MONITOR
package and how you might enable these traces.

Table 2





Program

Description

Notes

CLIENT_ID_STAT_ENABLE

Enable / Disable

Statistics
gathering for

a given
Client Identifier

  • Statistics
    can be seen through V$CLIENT_STATS.

CLIENT_ID_STAT_DISABLE

CLIENT_ID_TRACE_ENABLE

Enable / Disable

Tracing
for

a given
Client Identifier

  • Trace
    data may be written to many trace files as the trace may effect multiple
    connected sessions with the same client identifier.

CLIENT_ID_TRACE_DISABLE

SERV_MOD_ACT_STAT_ENABLE

Enable / Disable

Statistics
gathering for

(Service
Name/MODULE/ACTION)
combination.

  • Statistics
    can be seen through V$SERV_MOD_ACT_STATS.

SERV_MOD_ACT_STAT_DISABLE

SERV_MOD_ACT_TRACE_ENABLE

Enable / Disable

Tracing
for

(Service
Name/MODULE/ACTION) combination.

  • Trace
    data may be written to many trace files as the trace may effect multiple
    connected sessions.

SERV_MOD_ACT_TRACE_DISABLE

SESSION_TRACE_ENABLE

Enable / Disable

Tracing
for

a
session identifier (SID)

  • Session
    must exist

  • Traces
    only a given session

  • Enables
    / Disables a trace for the instance submitter is connected to.

SESSION_TRACE_EISABLE

Program

Example
How to Enable

CLIENT_ID_STAT_ENABLE

DBMS_MONITOR.CLIENT_ID_STAT_ENABLE

(client_identifier);

CLIENT_ID_STAT_DISABLE

CLIENT_ID_TRACE_ENABLE

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id, waits,  binds);

(Notes)

waits
: If TRUE, wait information is
present in the trace.

Binds
: If TRUE, bind information is
present in the trace.

CLIENT_ID_TRACE_DISABLE

SERV_MOD_ACT_STAT_ENABLE

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE
  
  (service_name, module_name, action_name);

(Notes)

Action_name, may be an empty field if not
defined for the session.

  • To
    reference ALL sessions that have an action_name defined, use the
    DBMS_MONITOR.ALL_ACTIONS variable.

  • To
    reference ALL sessions that do not have this variable defined, use NULL.

  • The
    contents of this field is actually ‘###ALL_ACTIONS’ and you should avoid
    naming any actions or module names with this.

SERV_MOD_ACT_STAT_DISABLE

SERV_MOD_ACT_TRACE_ENABLE

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
  
  (service_name, module_name, action_name, 
    waits, binds, instance_name);

(Notes)

Action_name, may be an empty field if not
defined for the session.

  • To
    reference ALL sessions that have an action_name defined, use the
    DBMS_MONITOR.ALL_ACTIONS variable.

  • To
    reference ALL sessions that do not have this variable defined, use NULL.

  • The
    contents of this field is actually ‘###ALL_ACTIONS’ and you should avoid
    naming any actions or module names with this.

waits
: If TRUE, wait information is
present in the trace.

Binds
: If TRUE, bind information is
present in the trace.

instance_name
restricts
tracing to the named instance_name.

SERV_MOD_ACT_TRACE_DISABLE

SESSION_TRACE_ENABLE

DBMS_MONITOR.SESSION_TRACE_ENABLE
  
  (session_id, serial_number, waits, binds)

(Notes)

session_id,
Must provide. If just a session_id is given than all serial numbers will be
traced. If session_id and serial_number are NULL than you trace your own
current session.

waits
: If TRUE, wait information is
present in the trace.

Binds
: If TRUE, bind information is
present in the trace.

SESSION_TRACE_DISABLE

Examples to Enable / Disable Tracing

Before you can enable or disable tracing you need to first
determine the connected session or group of sessions you would like to trace.
If you have set the CLIENT_IDENTIFIER, MODULE_NAME, or ACTION_NAME as prescribed
in the previous parts to this series you do not necessarily need to look for them,
as you should have a list that describes their actions. Although sometimes you
are tuning in a more adhoc environment where you are only concerned with what
is affecting your system currently. This could be an individual session or a
group of sessions that will be defined by their CLIENT_IDENTIFIER, MODULE_NAME,
ACTION_NAME, or SERVICE_NAME. To get a feel for what is executing currently on
your system you need only query the V$SESSION view. Listing 1 gives the
SQL and results of what you might see.

Listing 1


1 SELECT sid, serial#,
client_identifier, service_name, action, module
FROM V$SESSION

SID SERIAL# CLIENT_IDENTIFIER SERVICE_NAME ACTION MODULE
———- ——- ——————– ————- ———— ———–
145 34 george:127.0.0.1 ACCT INSERT MONTH PAYROLL
146 32 johnny:127.0.0.2 ACCT CANCEL CHECK BILLPAY
147 54 suzy:127.0.0.3 HR MODVACATION
156 64 lisa:127.0.0.4 HR INSERT HIST ADDEMPLOYEE

1.  Enable
statistics gathering for CLIENT_IDENTIFIER webclient.

EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE('WEBCLIENT');

2.  Enable
tracing for CLIENT_IDENTIFIER johnny:127.0.0.2
with wait information but no bind information

EXECUTE DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('johnny:127:0.0.2',TRUE,FALSE);

3.  Enable
statistics gathering for service_name ACCT, module PAYROLL, and all actions

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE('ACCT','PAYROLL',DBMS_MONITOR.ALL_ACTIONS);

4.  Enable
tracing for servide_name HR but only for the ADDEMPLOYEE module and include
wait and bind information.

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE('HR',
  'ADDEMPLOYEE', DBMS_MONITOR.ALL_ACTIONS,TRUE,TRUE,NULL);

5.  Enable
tracing for session id 142 and include wait and bind information

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(145,34, TRUE, TRUE);

6.  Trace
my current session

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL,NULL, TRUE, TRUE);

View your Enabled Tracing

To look at what you have enabled tracing and statistics
gathering for you need only look at the two views DBA_ENABLED_TRACES and
DBA_ENABLED_AGGREGATIONS. Listing 2 gives the output from selecting from
these two views for our examples. Notice that the examples 5 & 6 where we
started a trace for the sessions did not show up in Listing 2. These
must still be recognized by going to the user dump location on disk and looking
for the trace files generated.

Listing 2


SQL> select * from DBA_ENABLED_TRACES ;

TRACE_TYPE SERVICE MODULE ACTION WAITS BINDS INSTANCE_NAME
——————— ——————– ————- ———- —– —– —————-
CLIENT_ID johnny:127:0.0.2 TRUE FALSE
SERVICE_MODULE HR ADDEMPLOYEE TRUE TRUE

SQL> SELECT * FROM DBA_ENABLED_AGGREGATIONS ;

AGGREGATION SERVICE MODULE ACTION
——————– ——————– ——————– ——————–
CLIENT_ID webclient
SERVICE_MODULE ACCT PAYROLL

Enabling and disabling tracing
and statistical gathering has taken on a whole new meaning in Oracle 10g. It
has become easier to enable and manage with the DBMS_MONITOR package. We are
not strapped by the old methods of tracing, where we could just trace an
individual session, but can now trace across sessions by using the
CLIENT_IDENTIFIER, SERVICE_NAME, MODULE_NAME, and ACTION_NAME. Next time we
will look at where some of the actual tracing information resides and how to
query from internal tables in Oracle.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles