The v$session_longops view allows the Oracle DBA estimate the amount of time that is used by long-running DLL and DML statements. You can view any SQL statement that executes for more than 6 absolute seconds (the threshold).

The Oracle data dictionary contains a little-known view called the v$session_longops. You can query against the v$session_longops to quickly find out how much of that specific SQL statement has been completed.

Take note that the v$session_longops can also be used for any long-running operation including long-running updates.

Long Running SQL

Both column sofar and totalwork in v$session_longops will give you a brief idea on how many % of the work has completed in terms of block scanning.

The time remaining and elapsed seconds has the execution timing in details in terms of seconds. From here, you can roughly estimate how long your SQL query to complete.

The script below will display a status message that shows the current amount of time that has been used by long-running DDL operations.

Note that you must get the SID from v$session_longops and plug it into v$session to check the SQL command details.

SELECT sid, serial#, opname, target, sofar, totalwork, units, start_time, 
last_update_time, time_remaining "REMAIN SEC", round(time_remaining/60,2) "REMAIN MINS", 
elapsed_seconds "ELAPSED SEC", round(elapsed_seconds/60,2) "ELAPSED MINS", 
round((time_remaining+elapsed_seconds)/60,2)"TOTAL MINS", message TIME 
FROM v$session_longops
WHERE sofar<>totalwork
AND time_remaining <> '0'
 
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, v$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND b.sid = '11696'
ORDER BY a.spid, c.piece

Related Posts

• Long Running Concurrent Request In Oracle
• Check Current Long Running Jobs In Oracle Applications
• Kill Long Running Jobs In Oracle Applications
• Check Completed Long Running Jobs In Oracle Applications
• Monitor Oracle Session Waits