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

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