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