DBMS_APPLICATION_INFO to manage long-running operation

> Database > Oracle Database

Table of Contents

1 - About

Long running operation is defined as longer than three to five seconds.

If you execute a long-running command, a dynamic performance view V$SESSION_LONGOPS will be populated with information with the following information :

  • When the command started
  • How far it as progressed
  • Its estimated time to completion

DBMS_APPLICATION_INFO can help to answer this question :

  • What is the session doing, what form is it running, what code module is executing ?
  • How far along is that stored procedure ?
  • How far along is that batch job ?
  • What bin variable values where being used on that query ?

DBMS_APPLICATION_INFO allows you to set up to three columns in your row of the V$SESSION table :

  • the ACTION
  • the MODULE columns.

The value you set in the V$ tables are immediately visible. You do not need to commit them to see them, making them very useful for communcating with the outside world

You can use :

  • SET_CLIENT_INFO to store useful “state”
  • SET_MODULE/GET_MODULE to set the module when you enter and exit routines (at least the major). In this fashion, you can query V$SESSION and see what routine you are currently.
  • SET_SESSION_LONGOPS to store any routine that will take more than a couple of second

2 - Reference

db/oracle/dbms_application_info.txt · Last modified: 2017/09/06 19:27 by gerardnico