How long will a statement run? What is its I/O rate?

May 21, 2004

>>Script Language and Platform: Oracle
The v$session_longops table is an excellent table to determine how long a statment will run. This query will give the the current operation and its target. The biggest feature of this query is that it shows the I/O rate of the operation.

Author: Shawn Ellinger

column ssid format 9999 heading SID
column opname format a15 Heading Operation
column target format a28 Heading Target
column es format 999.9 Heading "Time|Ran"
column tr format 999.90 Heading "Time|Left"
column pct format 990 Heading "PCT"
column RATE Heading "I/O |Rate/m" just right

select 
sid ssid, 
substr(OPNAME,1,15) opname,
target, 
trunc((sofar/totalwork)*100) pct, 
to_char(60*sofar*8192/(24*60*(last_update_time - start_time))/1024/1024/60, '9999.0')||'M' Rate,
elapsed_seconds/60 es,
time_remaining/60 tr
from v$session_longops 
where time_remaining > 0
order by start_time
/

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home