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
   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.
|
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.