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

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

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id, waits,  binds);

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE
  
  (service_name, module_name, action_name);

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

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

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

EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE('WEBCLIENT');

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

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

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

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

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