Oracle dbms_application_info tips

Oracle Database Tips by Donald Burleson

The dbms_application_info procedure is used to add information to the v$session and v$session_longops, to allow you to track the execution time of long-running batch jobs.

The dbms_application_info package has these procedures:

set_modules: Registers the program and adds initial functionality.

set_action: Produces details on subsequent activity against Oracle tables.

set_client_info: Allows you t place additional documentation information about the execution program.

v$session view: Add three columns to v$session about the job module, action and client_info columns.

set_session_longops: For v$session_longops to work effectively, you must know the total amount of work you are planning to do and how much work you have done so far.

DBMS_APPLICATION_INFO.set_session_longops(rindex     => v_rindex,
                                         slno        => v_slno,
                                         op_name     => 'Batch Load',
                                         target      => v_obj,
                                         context     => 0,
                                         sofar       => v_sofar,
                                         totalwork   => v_totalwork,
                                         target_desc => 'BATCH_LOAD_TABLE',
                                         units       => 'rows processed');

v$session_longops view: For hv$session_longops to display ongoing details, you can ussie this SQL:

             SELECT
                opname,
                target_desc,
                sofar,
                totalwork,
                units
             FROM
                v$session_longops;

Setting up a Test Environment Using Oracle dbms_application_info

This database procedure uses the Oracle dbms_system package and the Oracle dbms_application_info packages to write a user defined string to the alert log at the start and end of the job. The body of the procedure loops 100 times with a sleep of one second in each loop. It uses the Oracle dbms_application_info package to write information to the v$session and v$session_longops views.

Explanations of the dbms_application_info and dbms_system packages are included as they can simplify the identification and monitoring of sessions related to scheduled jobs.

DBMS_APPLICATION_INFO.set_module(
module_name => 'my_job_proc',
action_name => p_text || ': Start.');

l_rindex := Dbms_Application_Info.Set_Session_Longops_Nohint;
l_total := 100;

FOR i IN 1 .. l_total LOOP
DBMS_APPLICATION_INFO.set_action(
action_name => p_text || ': Sleep ' || i || ' of ' || l_total || '.');

DBMS_APPLICATION_INFO.set_session_longops(
rindex => l_rindex,
slno => l_slno,

Description of the DBMS_APPLICATION_INFO package:

PROCEDURE DBMS_APPLICATION_INFO.READ_CLIENT_INFO

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
CLIENT_INFO                    VARCHAR2                OUT

PROCEDURE DBMS_APPLICATION_INFO.READ_MODULE

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

MODULE_NAME                    VARCHAR2                OUT

ACTION_NAME                    VARCHAR2                OUT

PROCEDURE DBMS_APPLICATION_INFO.SET_ACTION

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

ACTION_NAME                    VARCHAR2                IN

PROCEDURE DBMS_APPLICATION_INFO.SET_CLIENT_INFO

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

CLIENT_INFO                    VARCHAR2                IN

PROCEDURE DBMS_APPLICATION_INFO.SET_MODULE

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

MODULE_NAME                    VARCHAR2                IN

ACTION_NAME                    VARCHAR2                IN

PROCEDURE DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

RINDEX                         BINARY_INTEGER          IN/OUT

SLNO                           BINARY_INTEGER          IN/OUT

OP_NAME                        VARCHAR2                IN     DEFAULT

TARGET                         BINARY_INTEGER          IN     DEFAULT

CONTEXT                        BINARY_INTEGER          IN     DEFAULT

SOFAR                          NUMBER                  IN     DEFAULT

TOTALWORK                      NUMBER                  IN     DEFAULT

TARGET_DESC                    VARCHAR2                IN     DEFAULT

UNITS                          VARCHAR2                IN     DEFAULT

For more information on dbms_application_info, see the related links below:

DBMS_APPLICATION_INFO Package v$session_longops

DBMS_APPLICATION_INFO - Oracle shared pool high executions

For more dbms_application_info tips see:

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).

Buy direct from the publisher and save 30%!

��