#

| Chapter 11 Managing Session Information |

## 11.2 DBMS_System: Setting Events for Debugging

The DBMS_SYSTEM package contains procedures for setting special internal trace events that can help the DBA or Oracle Technical Support personnel diagnose and debug serious database problems.

The procedures in DBMS_SYSTEM are used by DBAs under special circumstances and should not be used by end users or coded into applications. Most of the procedures should be used only under specific instructions from Oracle Technical Support, as improper usage can actually crash or damage the database. The extremely useful SET_SQL_TRACE_IN_SESSION procedure, however, has less serious implications. Both DBAs and developers should be aware of and know how to use it.

### 11.2.1 Getting Started with DBMS_SYSTEM

The DBMS_SYSTEM package is created when the Oracle database is installed. The dbmsutil.sql script (found in the built-in packages source code directory, as described in Chapter 1) contains the source code for this package's specification. This script is called by catproc.sql, which is normally run immediately after database creation.

Unlike the other packages created by the dbmsutil.sql script, no public synonym for DBMS_SYSTEM is created, and no privileges on the package are granted. Thus, only the SYS user can normally reference and make use of this package. Other users (or roles) can be granted access to DBMS_SYSTEM by having the SYS user issue the following SQL command:

>     GRANT EXECUTE ON DBMS_SYSTEM TO username;

In practice, it is probably better and safer to create a cover package around DBMS_SYSTEM and grant EXECUTE privilege on the cover package to specific users or roles, as indiscriminate use of the DBMS_SYSTEM procedures can cause serious problems.

> NOTE: In Oracle 8.0, the DBMS_SYSTEM package specification was moved from the dbmsutil.sql script into prvtutil.plb (the "wrapped" file), protecting it further from inadvertent access.

Table 11.2 lists the programs provided by this package.

Table 11.2: DBMS_SYSTEM Programs
Name | Description | Use in SQL?
--- | --- | ---
READ_EV | Reads trace event level for current session | No
SET_EV | Sets trace event levels in user session | No
SET_SQL_TRACE_IN_SESSION | Sets SQL tracing on or off in user session | No

DBMS_SYSTEM does not declare any exceptions or any non-program elements.

### 11.2.2 DBMS_SYSTEM Interface

This section describes the programs defined in the DBMS_SYSTEM package.

#### 11.2.2.1 The DBMS_SYSTEM.READ_EV procedure

The READ_EV procedure is used to read trace event level settings for the current session. Here's the header for this program:

>     PROCEDURE DBMS_SYSTEM.READ_EV
>         (iev BINARY_INTEGER
>         ,oev OUT BINARY_INTEGER);

Parameters are summarized in the following table.

Parameter | Value
--- | ---
iev | Event number
oev | Event level

The program does not raise any exceptions.

#### 11.2.2.1.1 Restrictions

Note the following restrictions on calling READ_EV:

- The READ_EV procedure should not be invoked by end users or called by PL/SQL programs. Its usage is limited to Oracle internal purposes or under instruction of Oracle Technical Support. Improper use of the procedure by end users can have adverse effects on database performance.

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.2.2.1.2 Example

The following example[1] will display event-level settings for the current session:

> [1] Supplied by Ken Robinson of Oracle Technical Support.

>     /* Filename on companion disk: readev.sql */*
>     DECLARE
>     	event_level number;
>     BEGIN
>     	FOR i IN 10000..10999 LOOP
>     		DBMS_SYSTEM.READ_EV(i,event_level);
>     		IF (event_level > 0)
>     		THEN
>     			DBMS_OUTPUT.PUT_LINE('Event '||TO_CHAR(i)||' set at level '||
>     				TO_CHAR(event_level) );
>     		END IF;
>     	END LOOP;
>     END;
>     /

#### 11.2.2.2 The DBMS_SYSTEM.SET_EV procedure

The SET_EV procedure is used to set trace event levels in another user session. It is equivalent to having that session issue the ALTER SESSION SET EVENTS event syntax command. Since the other session may be engaged in an application, setting an event may not be possible. Here's the header for this program:

>     PROCEDURE DBMS_SYSTEM.SET_EV
>        (si BINARY_INTEGER
>        ,se BINARY_INTEGER
>        ,ev BINARY_INTEGER
>        ,le BINARY_INTEGER
>        ,nm IN VARCHAR2);

Parameters are summarized in the following table.

Parameter | Description
--- | ---
si | User session id
se | User session serial number
ev | Trace event number
le | Trace event level
nm | Trace event name

The program does not raise any exceptions.

#### 11.2.2.2.1 Restrictions

Note the following restrictions on calling SET_EV:

- The SET_EV procedure should not be invoked by end users or called by PL/SQL programs. Its usage is limited to Oracle internal purposes or under instruction of Oracle Technical Support. Improper use of the procedure by end users can have adverse effects on database performance or cause database crashes.

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

To learn more about Oracle trace events and their usage in diagnosing and debugging serious database problems, see the Oracle Backup & Recovery Handbook, by Rama Velpuri (Oracle Press, 1995).

#### 11.2.2.3 The DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION procedure

The SET_SQL_TRACE_IN_SESSION procedure enables or disables SQL tracing in another user's session. It is especially useful for analyzing and debugging runtime performance issues in applications. Here's the header for this program:

>     PROCEDURE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
>        (sid IN NUMBER
>        ,serial# IN NUMBER
>        ,sql_trace IN BOOLEAN);

Parameters are summarized in the following table.

Parameter | Value
--- | ---
sid | Session id
serial# | Session serial number
sql_trace | TRUE turns trace on, FALSE turns trace off

The program does not raise any exceptions, nor does it assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.2.2.3.1 Example

Set SQL tracing on in a session identified by sid = 15 and serial number = 4567 like this:

>     BEGIN
>        SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(15,4567,TRUE);
>     END;

The sid, serial number, and username for all sessions currently connected to the database can be obtained using the following query:

>     SELECT sid, serial#, username
>       FROM v$session;

Trace files generated when SQL tracing is turned on are created in the directory specified by the USER_DUMP_DEST initialization parameter for the Oracle database to which the session is connected. SQL trace files can be formatted for readability using the TKPROF utility. See the Oracle7 Server Tuning manual for instructions on using TKPROF.

### 11.2.3 DBMS_SYSTEM Examples

DBMS_SYSTEM has some mysterious and apparently dangerous procedures in it. Obtaining any information about SET_EV and READ_EV was very difficult and promises to be more difficult in the future since the package header is no longer exposed in Oracle 8.0.

In spite of Oracle's desire to keep DBMS_SYSTEM "under wraps," I feel strongly that the SET_SQL_TRACE_IN_SESSION procedure is far too valuable to be hidden away in obscurity. DBAs and developers need to find out exactly what is happening at runtime when a user is experiencing unusual performance problems, and the SQL trace facility is one of the best tools available for discovering what the database is doing during a user's session. This is especially useful when investigating problems with software packages where source code (including SQL) is generally unavailable.

So how can we get access to the one program in DBMS_SYSTEM we want without exposing those other dangerous elements to the public? The answer, of course, is to build a package of our own to encapsulate DBMS_SYSTEM and expose only what is safe. In the process, we can make DBMS_SYSTEM easier to use as well. Those of us who are "keyboard-challenged" (or just plain lazy) would certainly appreciate not having to type a procedure name with 36 characters.

I've created a package called trace to cover DBMS_SYSTEM and provide friendlier ways to set SQL tracing on or off in other user's sessions. Here is the package specification:

>     */ Filename on companion disk: trace.sql */*
>     CREATE OR REPLACE PACKAGE trace
>     IS
>        /*
>        || Exposes DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
>        || with easier to call programs
>        ||
>        || Author:  John Beresniewicz, Savant Corp
>        || Created: 07/30/97
>        ||
>        || Compilation Requirements:
>        || SELECT on SYS.V_$SESSION
>        || EXECUTE on SYS.DBMS_SYSTEM (or create as SYS)
>        ||
>        || Execution Requirements:
>        ||
>        */

>        /* turn SQL trace on by session id */
>        PROCEDURE Xon(sid_IN IN NUMBER);

>        /* turn SQL trace off by session id */
>        PROCEDURE off(sid_IN IN NUMBER);

>        /* turn SQL trace on by username */
>        PROCEDURE Xon(user_IN IN VARCHAR2);

>        /* turn SQL trace off by username */
>        PROCEDURE off(user_IN IN VARCHAR2);

>     END trace;

The trace package provides ways to turn SQL tracing on or off by session id or username. One thing that annoys me about DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION is having to figure out and pass a session serial number into the procedure. There should always be only one session per sid at any time connected to the database, so trace takes care of figuring out the appropriate serial number behind the scenes.

Another improvement (in my mind) is replacing the potentially confusing BOOLEAN parameter sql_trace with two distinct procedures whose names indicate what is being done. Compare the following commands, either of which might be used to turn SQL tracing off in session 15 using SQL*Plus:

>     SQL> execute trace.off(sid_IN=>15);

>     SQL> execute SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(15,4567,FALSE);

The first method is both more terse and easier to understand.

The xon and off procedures are both overloaded on the single IN parameter, with versions accepting either the numeric session id or a character string for the session username. Allowing session selection by username may be easier than by sids. Why? Because sids are transient and must be looked up at runtime, whereas username is usually permanently associated with an individual. Beware, though, that multiple sessions may be concurrently connected under the same username, and invoking trace.xon by username will turn tracing on in all of them.

Let's take a look at the trace package body:

>     /* Filename on companion disk: trace.sql */*
>     CREATE OR REPLACE PACKAGE BODY trace
>     IS

>        /*
>        || Use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to turn tracing on
>        || or off by either session id or username.  Affects all sessions
>        || that match non-NULL values of the user and sid parameters.
>        */
>        PROCEDURE set_trace
>           (sqltrace_TF BOOLEAN
>           ,user IN VARCHAR2 DEFAULT NULL
>           ,sid IN NUMBER DEFAULT NULL)
>        IS
>        BEGIN
>           /*
>           || Loop through all sessions that match the sid and user
>           || parameters and set trace on in those sessions.  The NVL
>           || function in the cursor WHERE clause allows the single
>           || SELECT statement to filter by either sid OR user.
>           */
>           FOR sid_rec IN
>              (SELECT sid,serial#
>                 FROM sys.v_$session   S
>                WHERE S.type='USER'
>                  AND S.username = NVL(UPPER(user),S.username)
>                  AND S.sid      = NVL(sid,S.sid) )
>           LOOP
>              SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
>                 (sid_rec.sid, sid_rec.serial#, sqltrace_TF);
>           END LOOP;
>        END set_trace;

>        /*
>        || The programs exposed by the package all simply
>        || call set_trace with different parameter combinations.
>        */
>        PROCEDURE Xon(sid_IN IN NUMBER)
>        IS
>        BEGIN
>           set_trace(sqltrace_TF => TRUE, sid => sid_IN);
>        END Xon;

>        PROCEDURE off(sid_IN IN NUMBER)
>        IS
>        BEGIN
>           set_trace(sqltrace_TF => FALSE, sid => sid_IN);
>        END off;

>        PROCEDURE Xon(user_IN IN VARCHAR2)
>        IS
>        BEGIN
>           set_trace(sqltrace_TF => TRUE, user => user_IN);
>        END Xon;

>        PROCEDURE off(user_IN IN VARCHAR2)
>        IS
>        BEGIN
>           set_trace(sqltrace_TF => FALSE, user => user_IN);
>        END off;

>     END trace;

All of the real work done in the trace package is contained in a single private procedure called set_trace. The public procedures merely call set_trace with different parameter combinations. This is a structure that many packages exhibit: private programs with complex functionality exposed through public programs with simpler interfaces.

One interesting aspect of set_trace is the cursor used to get session identification data from V_$SESSION. I wanted to identify sessions for tracing by either session id or username. I could have just defined two cursors on V_$SESSION with some conditional logic deciding which cursor to use, but that just did not seem clean enough. After all, less code means fewer bugs. The solution I arrived at: make use of the NVL function to have a single cursor effectively ignore either the sid or the user parameter when either is passed in as NULL. Since set_trace is always called with either sid or user, but not both, the NVLs act as a kind of toggle on the cursor. I also supplied both the sid and user parameters to set_trace with the default value of NULL so that only the parameter being used for selection needs be passed in the call.

Once set_trace was in place, the publicly visible procedures were trivial.

A final note about the procedure name "xon": I wanted to use the procedure name "on," but ran afoul of the PL/SQL compiler since ON is a reserved word in SQL and PL/SQL.

11.1 DBMS_SESSION: Managing Session Information |  | 12. Managing Server Resources

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.