Oracle7 Tuning, release 7.3.3 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Registering Applications


Application developers can use the DBMS_APPLICATION_INFO package 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:

Overview

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.

Registering Applications

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

DBMS_APPLICATION_INFO Package

The following table lists the procedures available in the DBMS_APPLICATION_INFO package.

Table 23-1: Procedures in the DBMS_APPLICATION_INFO Package
Procedure   Description   Discussed on  

SET_MODULE

 

Sets the name of the module that is currently running.

 

page 23-4

 

SET_ACTION

 

Sets the name of the current action within the current module.

 

page 23-5

 

SET_CLIENT_INFO

 

Sets the client information field for the session.

 

page 23-6

 

READ_MODULE

 

Reads the values of the module and action fields for the current session.

 

page 23-8

 

READ_CLIENT_INFO

 

Reads the client information field for the current session.

 

page 23-8

 

Privileges

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 Oracle7 Server Application Developer's Guide.

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 be descriptive text about the action the module is performing.

Example

The following example 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

The parameters for the SET_MODULE procedure are described in Table 23-2. The syntax for this procedure is shown below:

DBMS_APPLICATION_INFO.SET_MODULE(
module_name IN VARCHAR2,
action_name IN VARCHAR2)
Table 23-2: Parameters for SET_MODULE Procedure
Parameter   Description  

module_name

 

The name of the 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

 

The name of the 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 Table 23-3. The syntax for this procedure is shown below:

DBMS_APPLICATION_INFO.SET_ACTION(action_name IN VARCHAR2)

Table 23-3: Parameters for SET_ACTION Procedure
Parameter   Description  

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 Table 23-4. The syntax for this procedure is shown below:

DBMS_APPLICATION_INFO.SET_CLIENT_INFO(client_info IN VARCHAR2)

Table 23-4: Parameters for SET_CLIENT_INFO Procedure
Parameter   Description  

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

READ_MODULE Syntax

The parameters for the READ_MODULE procedure are described in Table 23-5. The syntax for this procedure is shown below:

DBMS_APPLICATION_INFO.READ_MODULE(
module_name OUT VARCHAR2,
action_name OUT VARCHAR2)
Table 23-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

 

READ_CLIENT_INFO Syntax

The parameter for the READ_CLIENT_INFO procedure is described in Table 23-6. The syntax for this procedure is shown below:

DBMS_APPLICATION_INFO.READ_CLIENT_INFO(client_info OUT VARCHAR2)

Table 23-6: Parameters for READ_CLIENT_INFO Procedure
Parameter   Description  

client_info

 

The last client information value supplied to the SET_CLIENT_INFO procedure.

 



Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index