5
Registering Applications

Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record names of executing modules or transactions in the database for later use when tracking the performance of various modules. This chapter describes how to register an application with the database and retrieve statistics on each registered module or code segment.

Oracle provides a method for applications to register the name of the application and actions performed by that application with the database. Registering the application allows system administrators and performance tuning specialists to track performance by module. System administrators can also use this information to track resource use by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views.

Your applications should set the name of the module and name of the action automatically each time a user enters that module. The module name could be the name of a form in an Oracle Forms application, or the name of the code segment in an Oracle precompilers application. The action name should usually be the name or description of the current transaction within a module.

Topics in this chapter include:

• Setting the Module Name

• Setting the Action Name

• Setting the Client Information

• Retrieving Application Information

DBMS_APPLICATION_INFO Package

To register applications with the database, use the procedures in the DBMS_APPLICATION_INFO package. DBMS_APPLICATION_INFO provides the following procedures:

Table 5-1 Procedures in the DBMS_APPLICATION_INFO Package

Procedure	Description
SET_MODULE	Sets the name of the module that is currently running.
SET_ACTION	Sets the name of the current action within the current module.
SET_CLIENT_INFO	Sets the client information field for the session.
READ_MODULE	Reads values of module and action fields for the current session.
READ_CLIENT_INFO	Reads the client information field for the current session.

Privileges

Before using this package, you must run the DBMSUTL.SQL script to create the DBMS_APPLICATION_INFO package.

See Also: For more information about Oracle supplied packages and executing stored procedures, see the Oracle8i Supplied Packages Reference.

Setting the Module Name

To set the name of the current application or module, use the SET_MODULE procedure in the DBMS_APPLICATION_INFO package. The module name should be the name of the procedure (if using stored procedures), or the name of the application. The action name should describe the action performed.

Example

The sample PL/SQL block in the following SQL statement, starting at the BEGIN keyword, sets the module name and action name:

 CREATE PROCEDURE add_employee(
    name       VARCHAR2(20),
    salary     NUMBER(7,2),
    manager    NUMBER,
    title      VARCHAR2(9),
    commission NUMBER(7,2),
    department NUMBER(2))  AS
 BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE(
       module_name => 'add_employee',
       action_name => 'insert into emp');
   INSERT INTO emp 
      (ename, empno, sal, mgr, job, hiredate, comm, deptno)
      VALUES (name, next.emp_seq, manager, title, SYSDATE, 
              commission, department);
   DBMS_APPLICATION_INFO.SET_MODULE('','');
 END;

Syntax

Syntax and parameters for the SET_MODULE procedure are described here:

 DBMS_APPLICATION_INFO.SET_MODULE(
    module_name   IN VARCHAR2,
    action_name   IN VARCHAR2)

module_name	Name of module that is currently running. When the current module terminates, call this procedure with the name of the new module if there is one, or null if there is not. Names longer than 48 bytes are truncated.
action_name	Name of current action within the current module. If you do not want to specify an action, this value should be null. Names longer than 32 bytes are truncated.

Setting the Action Name

To set the name of the current action within the current module, use the SET_ACTION command in the DBMS_APPLICATION_INFO package. The action name should be descriptive text about the current action being performed. You should probably set the action name before the start of every transaction.

Example

The following is an example of a transaction that uses the registration procedure:

 CREATE OR REPLACE PROCEDURE bal_tran (amt IN NUMBER(7,2)) AS
 BEGIN
-- balance transfer transaction
    DBMS_APPLICATION_INFO.SET_ACTION(
      action_name => 'transfer from chk to sav');
    UPDATE chk SET bal = bal + :amt 
      WHERE acct# = :acct;
    UPDATE sav SET bal = bal - :amt
      WHERE acct# = :acct;
    COMMIT;
    DBMS_APPLICATION_INFO.SET_ACTION('');
 END;

Set the transaction name to null after the transaction completes so that subsequent transactions are logged correctly. If you do not set the transaction name to null, subsequent transactions may be logged with the previous transaction's name.

Syntax

The parameter for the SET_ACTION procedure is described in this section. The syntax for this procedure is shown below:

 DBMS_APPLICATION_INFO.SET_ACTION(action_name IN VARCHAR2)

action_name

The name of the current action within the current module. When the current action terminates, call this procedure with the name of the next action if there is one, or null if there is not. Names longer than 32 bytes are truncated.

Setting the Client Information

To supply additional information about the client application, use the SET_CLIENT_INFO procedure in the DBMS_APPLICATION_INFO package.

Syntax

The parameter for the SET_CLIENT_INFO procedure is described in this section. The syntax for this procedure is shown below:

 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info IN VARCHAR2)

client_info

Use this parameter to supply any additional information about the client application. This information is stored in the V$SESSIONS view. Information exceeding 64 bytes is truncated.

Retrieving Application Information

Module and action names for a registered application can be retrieved by querying V$SQLAREA or by calling the READ_MODULE procedure in the DBMS_APPLICATION_INFO package. Client information can be retrieved by querying the V$SESSION view, or by calling the READ_CLIENT_INFO procedure in the DBMS_APPLICATION_INFO package.

Querying V$SQLAREA

The following sample query illustrates the use of the MODULE and ACTION column of the V$SQLAREA.

   SELECT sql_text, disk_reads, module, action 
     FROM v$sqlarea 
     WHERE module = 'add_employee';

SQL_TEXT                  DISK_READS  MODULE             ACTION 
-------------------       ----------  ------------------ ----------------
INSERT INTO emp           1           add_employee       insert into emp
(ename, empno, sal,
mgr, job, hiredate,
comm, deptno)
VALUES 
(name, 
next.emp_seq,
manager, title, 
SYSDATE, commission,
department) 

1 row selected.

READ_MODULE Syntax

The parameters for the READ_MODULE procedure are described in this section. The syntax for this procedure is shown below:

 DBMS_APPLICATION_INFO.READ_MODULE(
    module_name   OUT   VARCHAR2,
    action_name   OUT   VARCHAR2)

module_name	The last value that the module name was set to by calling SET_MODULE.
action_name	The last value that the action name was set to by calling SET_ACTION or SET_MODULE.

READ_CLIENT_INFO Syntax

The parameter for the READ_CLIENT_INFO procedure is described in this section. The syntax for this procedure is shown below:

 DBMS_APPLICATION_INFO.READ_CLIENT_INFO(client_info OUT VARCHAR2)

client_info

The last client information value supplied to the SET_CLIENT_INFO procedure.

---