Release 8.0
A54638-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8(TM) Server Tuning Release 8.0 A54638-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 usage 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 Server 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;
The parameters for the SET_MODULE procedure are described in Table 24-2. The syntax for this procedure is shown below:
DBMS_APPLICATION_INFO.SET_MODULE(
module_name IN VARCHAR2, action_name IN VARCHAR2)Table 24-2: Parameters for SET_MODULE Procedure
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 Table 24-3. The syntax for this procedure is shown below:
DBMS_APPLICATION_INFO.SET_ACTION(action_name IN VARCHAR2)Table 24-3: Parameter for SET_ACTION Procedure
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 Table 24-4. The syntax for this procedure is shown below:
DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info IN VARCHAR2)Table 24-4: Parameter for SET_CLIENT_INFO Procedure
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 is a sample query illustrating 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 Table 24-5. The syntax for this procedure is shown below:
DBMS_APPLICATION_INFO.READ_MODULE(
module_name OUT VARCHAR2,
action_name OUT VARCHAR2)Table 24-5: Parameters for READ_MODULE Procedure
| Parameter | Description |
|---|---|
|
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 Table 24-6. The syntax for this procedure is shown below:
DBMS_APPLICATION_INFO.READ_CLIENT_INFO(client_info OUT VARCHAR2)Table 24-6: Parameter for READ_CLIENT_INFO Procedure
| Parameter | Description |
|---|---|
|
client_info |
The last client information value supplied to the SET_CLIENT_INFO procedure. |
