v$session_longops Oracle Tips by Burleson Consulting

You can view any SQL statement that executes for more than 6 absolute seconds (the threshold) using the v$session_longops view.

 

The Oracle data dictionary contains a little-known view called the v$session_longops. The v$session_longops view allows the Oracle professional contract the amount of time that is used by long-running DLL and DML statements.

You can query against the v$session_longops to quickly find out how much of that specific DL statement has been completed. Also note that the v$session_longops can also be used for any long-running operation, including long-running updates.

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 and plug it into the SQL statement below:

 

select * from
(
  select
     opname,
     start_time,
     target,
     sofar,
     totalwork,
     units,
     elapsed_seconds,
     message
   from
        v$session_longops
  order by start_time desc
)
where rownum <=1;

select
   sid,
   message
from
   v$session_longops
where
   sid = 13
order by
   start_time;

Here is a sample of the output from v$session_longops, showing the progress of a long running CREATE INDEX statement.

SID MESSAGE

--- -----------------------------------------------------
11 Table Scan: CUST.PK_IDX: 732 out of 243260 Blocks done

The set_session_longops procedure can be used to publish information about the progress of long operations by inserting and updating rows in the v$session_longops view.

PROCEDURE set_session_longops (
  rindex       IN OUT  PLS_INTEGER,
  slno         IN OUT  PLS_INTEGER,
  op_name      IN      VARCHAR2    DEFAULT NULL,
  target       IN      PLS_INTEGER DEFAULT 0,
  context      IN      PLS_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)

It is especially useful when operations contain long running loops such as shown in the example below.

DECLARE
  l_rindex     PLS_INTEGER;
  l_slno       PLS_INTEGER;
  l_totalwork  NUMBER;
  l_sofar      NUMBER;
  l_obj        PLS_INTEGER;
BEGIN
  l_rindex    := DBMS_APPLICATION_INFO.set_session_longops_nohint;
  l_sofar     := 0;
  l_totalwork := 10;

  WHILE l_sofar < 10 LOOP
    -- Do some work
    DBMS_LOCK.sleep(5);

    l_sofar := l_sofar + 1;

    DBMS_APPLICATION_INFO.set_session_longops(
      rindex      => l_rindex,
      slno        => l_slno,
      op_name     => 'BATCH_LOAD',
      target      => l_obj,
      context     => 0,
      sofar       => l_sofar,
      totalwork   => l_totalwork,
      target_desc => 'BATCH_LOAD_TABLE',
      units       => 'rows');
      SEE CODE DEPOT FOR FULL SCRIPT
  END LOOP;
END;
/

While the above code is running, the contents of the v$session_longops view can be queried as follows.

COLUMN opname FORMAT A20
COLUMN target_desc FORMAT A20
COLUMN units FORMAT A10

SELECT
   opname,
   target_desc,
   sofar,
   totalwork,
   time_remaining,
    units
FROM  
   v$session_longops
SEE CODE DEPOT FOR FULL SCRIPT

The type of output expected from this v$session_longops query is listed below.

OPNAME               TARGET_DESC               SOFAR  TOTALWORK UNITS
-------------------- -------------------- ---------- ---------- ----
BATCH_LOAD           BATCH_LOAD_TABLE              3         10 rows

 

Using v$session_longops to monitor a job's progress

You can run this query every n seconds to watch the progress of a ling running job or submit a shell script that samples the v$session_longops table every 30 seconds and writes the output to a flat file that you can see with the "tail -f" command.

Changes to v$session_longops

More operations are being added to v$session_longops. This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release. In the 10g release, this view keeps track of ROLLBACK and ROLLBACK TO operations also.

For a working example of an Oracle shell script to monitor the progress of a long-running job, please see the book Oracle Shell Scripting by Jon Emmons.