Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

   

Oracle dbms_application_info tips

Oracle Tips by Burleson Consulting
 

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 oroicedyres:

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%!


 

 

��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.