v$session_longops tips

Oracle Database Tips by Donald BurlesonAugust 4, 2015

The v$session_longops view allows the Oracle professional contract the amount of time that is used by long-running SQL, DLL and DML statements.

Also, in order for SQL queries to appear in v$session_longops:

- There must be a full-table scan again the target table
- The table scan must run more than six seconds wall clock seconds
- The table must consume at least 10,000 database blocks.

The v$session_longops is also useful for monitoring DDL and DML.

For example, in the data warehouse environment, building a multi-gigabyte large index can take many hours even with parallel index creation. You can query against the v$session_longops to quickly find out how much of that specific DDL 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 operations, include the associated SQL from the v$sql view.

select
   l.sid,
   l.sofar,
   l.totalwork,
   l.start_time,
   l.last_update_time,
   s.sql_text
from
   v$session_longops l
left outer join
   v$sql s
on
   s.hash_value = l.sql_hash_value
and
   s.address = l.sql_address
and
   s.child_number = 0;

You can also see v$session_longops with session details by joining into v$session. 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, showing the progress of a long running CREATE INDEX statement.

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

Using v$session_longops with set session longops

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');
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,
       units
FROM   v$session_longops
WHERE opname = 'BATCH_LOAD';

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

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

Laurent Schneider

provides the following 
	query for very long-running operations:



	
col target for a20
set lin 150 pages 40000 termout off
alter 
	session set nls_currency='%';
col PCT_DONE for 990.00L jus r
col 
	time_remaining for 999999

select
  
	lo.target,lo.sofar,seg.blocks,
  lo.ELAPSED_SECONDS*seg.blocks/lo.sofar-lo.ELAPSED_SECONDS 
	TIME_REMAINING,
  100*lo.sofar/seg.blocks PCT_DONE
from
  
	dba_segments      seg,
  v$session_longops 
	lo
where
  lo.units='Blocks'
  and lo.totalwork>0 and (lo.time_remaining>0 
	or lo.time_remaining is null)
  and regexp_substr(lo.target,'[^.]+') 
	= seg.owner
  and regexp_substr(lo.target,'[^.]+$') = 
	seg.segment_name;

��