How to determine the approximate amount of SQL work left in Oracle?
Problem:
You want to know how much longer a long running SQL might ake to finish.
Solution
We can use “V$SESSION_LONGOPS" view to know the approximate time of a query left to execute. "V$SESSION_LONGOPS” view displays the status of various database operations that have been running for longer than six seconds. Please note that this view give you only a rough estimate of when a SQL might complete.
Example
select
a.username
,a.opname
,b.sql_text
,to_char(a.start_time,'DD-MON-YY HH24:MI') start_time
,a.elapsed_seconds how_long
,a.time_remaining secs_left
,a.sofar
,a.totalwork
,round(a.sofar/a.totalwork*100,2) percent
from v$session_longops a
,v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value
and a.sofar <> a.totalwork
and a.totalwork != 0;
In order, to view the results properly via SQLPLUS, set he below parameters first.
Example
SET LINESIZE 141 TRIMSPOOL ON PAGES 66
COL username FORMAT A8 HEAD "User|Name"
COL opname FORMAT A16 HEAD "Operation|Type"
COL sql_text FORMAT A33 HEAD "SQL|Text" TRUNC
COL start_time FORMAT A15 HEAD "Start|Time"
COL how_long FORMAT 99,990 HEAD "Time|Run"
COL secs_left FORMAT 99,990 HEAD "Appr.|Secs Left"
COL sofar FORMAT 9,999,990 HEAD "Work|Done"
COL totalwork FORMAT 9,999,990 HEAD "Total|Work"
COL percent FORMAT 999.90 HEAD "%|Done"
select
a.username
,a.opname
,b.sql_text
,to_char(a.start_time,'DD-MON-YY HH24:MI') start_time
,a.elapsed_seconds how_long
,a.time_remaining secs_left
,a.sofar
,a.totalwork
,round(a.sofar/a.totalwork*100,2) percent
from v$session_longops a
,v$sql b
where a.sql_address = b.address
and a.sql_hash_value = b.hash_value?
and a.sofar <> a.totalwork
and a.totalwork != 0;
Published on 05-Dec-2020 10:52:48
