Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational