Oracle8 Tuning Release 8.0 A58246-01 |
|
Application developers can use the DBMS_APPLICATION_INFO package with Oracle Trace and the SQL trace facility to record the name of the executing module or transaction in the database for use later 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. Topics in this chapter include:
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.
To register applications with the database, use the procedures in the DBMS_APPLICATION_INFO package.
DBMS_APPLICATION_INFO provides the following procedures:
Before using this package, you must run the DBMSUTL.SQL script to create the DBMS_APPLICATION_INFO package. For more information about Oracle supplied packages and executing stored procedures, see the Oracle8 Application Developer's Guide.
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.
The following sample PL/SQL block 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 and parameters for the SET_MODULE procedure are described here:
DBMS_APPLICATION_INFO.SET_MODULE( module_name IN VARCHAR2, action_name IN VARCHAR2)
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.
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.
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)
To supply additional information about the client application, use the SET_CLIENT_INFO procedure in the DBMS_APPLICATION_INFO package.
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. |
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.
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.
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 |
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. |