Oracle7 Server Tuning

Library

Product

Contents

Index

Retrieving Application Information

Registering Applications
Overview
Registering Applications
Setting the Module Name
Setting the Action Name
Setting the Client Information
Retrieving Application Information

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.

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)


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)


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)


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)


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)


Parameter




Description







client_info



The last client information value supplied to the SET_CLIENT_INFO procedure.

Prev Next

Library

Product

Contents

Index

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.

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

Procedure

Description

Discussed on

Parameter

Description

Parameter

Description

Parameter

Description

Parameter

Description

Parameter

Description