3
DBMS_APPLICATION_INFO

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.

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.

If you want to gather you own statistics based on module, then you can implement a wrapper around this package by writing a version of this package in another schema that first gathers statistics and then calls the SYS version of the package. The public synonym for DBMS_APPLICATION_INFO can then be changed to point to the DBA's version of the package.

Privileges

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

Summary of Subprograms

Table 3-1 DBMS_APPLICATION_INFO Package Subprograms

SET_MODULE procedure

SET_ACTION procedure

READ_MODULE procedure

SET_CLIENT_INFO 
procedure

READ_CLIENT_INFO 
procedure

SET_SESSION_LONGOPS 
procedure

SET_MODULE procedure

This procedure sets the name of the current application or module. 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.

Syntax

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

Parameters

Table 3-2 SET_MODULE Procedure Parameters

module_name

action_name

Example

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; 

SET_ACTION procedure

This procedure sets the name of the current action within the current module. 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.

Syntax

DBMS_APPLICATION_INFO.SET_ACTION (
   action_name IN VARCHAR2); 

Parameters

Table 3-3 SET_ACTION Procedure Parameters

action_name

Usage Notes

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, then subsequent transactions may be logged with the previous transaction's name.

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;  

READ_MODULE procedure

This procedure reads the values of the module and action fields of the current session.

Syntax

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

Parameters

Table 3-4 READ_MODULE Procedure Parameters

module_name

action_name

Usage Notes

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

Example

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.

SET_CLIENT_INFO procedure

This procedure supplies additional information about the client application.

Syntax

DBMS_APPLICATION_INFO.SET_CLIENT_INFO (
   client_info IN VARCHAR2); 

Parameters

Table 3-5 SET_CLIENT_INFO Procedure Parameters

client_info

READ_CLIENT_INFO procedure

This procedure reads the value of the client_info field of the current session.

Syntax

DBMS_APPLICATION_INFO.READ_CLIENT_INFO (
   client_info OUT VARCHAR2); 

Parameters

Table 3-6 READ_CLIENT_INFO Procedure Parameters

client_info

SET_SESSION_LONGOPS procedure

This procedure sets a row in the V$SESSION_LONGOP table. This is a table which is customarily used to indicate the on-going progress of a long running operation. Some Oracle functions, such as Parallel Query and Server Managed Recovery, use rows in this table to indicate the status of, for example, a database backup.

Applications may use this function to advertise information about application-specific long running tasks.

Syntax

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS (
   rindex      IN OUT PLS_INTEGER,
   slno        IN OUT PLS_INTEGER,
   op_name     IN     VARCHAR2    DEFAULT NULL,
   target      IN     PLS_INTEGER DEFAULT 0,
   context     IN     PLS_INTEGER DEFAULT 0,
   sofar       IN     NUMBER      DEFAULT 0,
   totalwork   IN     NUMBER      DEFAULT 0,
   target_desc IN     VARCHAR2    DEFAULT 'unknown target',
   units       IN     VARCHAR2    DEFAULT NULL)  

set_session_longops_nohint constant pls_integer := -1;

Pragmas

pragma TIMESTAMP('1998-03-12:12:00:00');

Parameters

Table 3-7 SET_SESSION_LONGOPS Procedure Parameters

rindex

slno

op_name

target

context

sofar

totalwork

target_desc

units