Table of Contents

oracle debug

About

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

If you execute a long-running command, a dynamic performance view VSESSION_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 VSESSION table :

  • the CLIENT_INFO
  • 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 VSESSION and see what routine you are currently.
  • SET_SESSION_LONGOPS to store any routine that will take more than a couple of second

Reference