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.

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.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.