Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Dec 15, 2004

Oracle Session Tracing Part III

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date