Most of the long running SQL’s can be monitored by using the oracle view v$session_longops. This view is not to be queried in standalone. Rather, you should identify the session that you want to monitor using the v$session and the use session_id as a filter. I also add a condition time_remaining > 0 so that it lists only the active ones.
SQL to Monitor v$session_longops
SELECT SID, ROUND(TIME_REMAINING /60,2)"time remaining(min)", ROUND(ELAPSED_SECONDS/60,2)"elapsed_mins", SOFAR, TOTALWORK-SOFAR "Work Remaining" , SQL_PLAN_OPTIONS||' '||SQL_PLAN_OPERATION "PLAN OPERATION", MESSAGE FROM V$SESSION_LONGOPS WHERE TIME_REMAINING>0;
There are a number of criteria which makes a SQL to be listed in view V$SESSION_LONGOPS, and I am not sure of all of them. In case of Full Table Scans, the scan has to run for at least 6 seconds and the table scanned should occupy at least 10000 db_blocks. The view also lists long operations in hash_joins, sorts, sort outputs, gather table stats and gather tables index stats. The 6 second criteria appears to be applicable across all of it while the 10K block occupancy is limited to the Table Scan alone.
Note that the time_remaining column in the view should never be considered to predict the “actual” time remaining. In most cases, especially involving hash_joins the time increases rather exponentially than linear. It depends on the hash_area_size (AUTO by default in 11g) and the size of the blocks to be processed. And if it starts using TEMP to do the sorts and you know that there is a large amount of data, you should probably start tweaking the SQL 🙂
When executing DML (DELETE & UPDATE), in addition to the rows in the table, oracle has to update the indexes on the table. The time spent in doing the index modification is not reflected properly in the v$session_longops view as it looks at table blocks only. The estimate will also be based on table blocks and hence will be lower than the actual value required.
The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS can be used to add this facility to custom applications. Once this is set, oracle will display the information about that session in the view. For it 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 IN OUT BINARY_INTEGER, slno IN OUT BINARY_INTEGER, op_name IN VARCHAR2 DEFAULT NULL, target IN BINARY_INTEGER DEFAULT 0, context IN BINARY_INTEGER DEFAULT 0, sofar IN NUMBER DEFAULT 0, totalwork IN NUMBER DEFAULT 0, target_desc IN VARCHAR2 DEFAULT 'unknown target', units IN VARCHAR2 DEFAULT NULL) ; set_session_longops_nohint constant BINARY_INTEGER := -1;
Syntax For the above referenced from Oracle Documentation