Donald K. Burleson Oracle Tips

Tracking long-running DDL statements

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

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

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;

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
 

If you like Oracle tuning, check out my latest book "Oracle Tuning: The Definitive Reference".  It's 980 pages of hard-core tuning insights, tips and scripts, and you can buy it direct from the publisher for 30%-off. Best of all, you get instant access to the code depot of Oracle tuning scripts.

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.