home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 6.2 UTL_FILE: Reading and Writing Server-side Files Chapter 7 Next: 7.2 DBMS_APPLICATION_INFO Interface
 

7. Defining an Application Profile

The DBMS_APPLICATION_INFO package provides procedures that allow applications to "register" their current execution status with the Oracle database. Once registered, information about the status of an application can be monitored externally through several of the V$ virtual tables.

DBMS_APPLICATION_INFO is used to develop applications that can be monitored in various ways, including the following:

  • Module usage (where do users spend their time in the application?)

  • Resource accounting by transaction and module

  • End-user tracking and resource accounting in three-tier architectures

  • Incremental recording of long-running process s tatistics

When applications register themselves using DBMS_APPLICATION_INFO, DBAs and developers are able to analyze their performance and resource consumption much more closely. This facilitates better application tuning and enables more accurate usage-based cost accounting.

WARNING: Oracle explicitly warns that DBMS_APPLICATION_INFO should not be used in Trusted Oracle databases.

7.1 Getting Started with DBMS_APPLICATION_INFO

In Oracle 7.3, the DBMS_APPLICATION_INFO package is created when the Oracle database is installed. The dbmsutil.sql script found in the built-in packages source code directory (as described in Chapter 1, Introduction ) contains the source code for this package's specification. In Oracle 8.0, the script dbmsapin.sql (also found in the source code directory) creates the package. In either case, the scripts are called by catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DBMS_APPLICATION_INFO for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

7.1.1 DBMS_APPLICATION_INFO Programs

Table 7.1 lists the programs available from DBMS_APPLICATION_INFO.


Table 7.1: DBMS_APPLICATION_INFO Programs

Name

Description

Use in

SQL?

READ_CLIENT_INFO

Reads client information for session

No

READ_MODULE

Reads module and action for current session

No

SET_ACTION

Sets action within module

No

SET_CLIENT_INFO

Sets client information for session

No

SET_MODULE

Sets name of module executing

No

SET_SESSION_LONGOPS

Sets row in LONGOPS table (Oracle 8.0 only)

No

DBMS_APPLICATION_INFO does not declare any exceptions.

7.1.2 The V$ Virtual Tables

Most of the programs in DBMS_APPLICATION_INFO modify the V$ virtual tables to register application status. Table 7.2 lists the V$ tables and columns that each program modifies.


Table 7.2: V$ Tables and Columns Modified by DBMS_APPLICATION_INFO

Program

V$ Table.Column

SET_ACTION

V$SQLAREA.ACTION

SET_CLIENT_INFO

V$SESSION.CLIENT_INFO

SET_MODULE

V$SESSION.MODULE

V$SQLAREA.MODULE

V$SESSION.ACTION

V$SQLAREA.ACTION

SET_SESSION_LONGOPS

V$SESSION_LONGOPS.APPLICATION_DATA_3

7.1.3 DBMS_APPLICATION_INFO Nonprogram Elements

The DBMS_APPLICATION_INFO package contains a single constant: set_session_longops_nohint. This constant is defined like this:

set_session_longops_nohint CONSTANT BINARY_INTEGER := -1;

This constant is used as a special value for the hint parameter of the SET_SESSION_LONGOPS procedure. When this value is passed, a new row in the V$SESSION_LONGOPS virtual table is acquired for tracking long operations. (See the example in the section, " Section 7.2.6, "The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure" .")


Previous: 6.2 UTL_FILE: Reading and Writing Server-side Files Oracle Built-in Packages Next: 7.2 DBMS_APPLICATION_INFO Interface
6.2 UTL_FILE: Reading and Writing Server-side Files Book Index 7.2 DBMS_APPLICATION_INFO Interface

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference