Logo Background

Oracle Session Longops – Long Running SQL

  • By on August 7, 2010 | No Comments

    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.sid = '11696'
    ORDER BY a.spid, c.piece
    » Alter Table Shrink Space In Oracle
Leave a Comment