Oracle Session Tracing Part III

Author   : James F. Koopmann

More at  : www.jameskoopmann.com

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 has always been three session environment variables that get set from when a user connects to the database and are important when talking about tracing a particular session or group of sessions. These would be the SID, SERIAL#, and SERVICE_NAME. These have been around Oracle sessions for quite some time but are presented in Table 1 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, all our six variables are represented for us to begin tracing through. Table 3 gives a quick reference guide on 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 effecting your system currently. This could be an individual session or a group of sessions which 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 take a look at what you have enabled tracing and statistics gathering for you need only take a 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.