Get a rough estimate when an Oracle long operation will finish

Look at v$session_longops to get the sid of the long running operation for the appropriate user. In these examples, I'm using user SCOTT. Use the sid to target the specific row.

Do a couple simple queries to estimate how many units of work are completed every 10 minutes:

select sofar, totalwork, totalwork - sofar work_remaining, units
from v$session_longops l
where username = 'SCOTT'
and sid = 390
; 

Just repeat the above query every 10 minutes noting the amount remaining. Do the math to get an average. This gave me a very rough 22 blocks every 10 minutes.

ETA

Then, plug in the average and the time as appropriate in the query below to display the estimated time of completion.

with
cte_longops as (
select 
 totalwork - sofar work_remaining
from v$session_longops l
where username = 'SCOTT'
and sid = 390
)
,cte_hours as (
select
 (((work_remaining / 22) * 10) / 60) hours_to_go
from cte_longops  
)
select 
  round(hours_to_go,1) hours_to_go
 ,to_char(sysdate + (hours_to_go) / 24, 'D Mon YYYY HH:MI:SS AM') eta
from cte_hours
;

If your eta is roughly consistent, your average is probably good enough.

Improve the estimate

If your are lazy but still want to improve the average, here's a starting point. You will need to tweak wait_minutes and stop_at as appropriate for your particular operation. For mine, 10 minutes was needed to get a decent delta for blocks processed.

set serveroutput on;
declare
 units_prev int default null;
 units_now int default 0;
 stop_at int default 5;
 wait_seconds int default 60*10; 
 delta int default 0;
 total int default 0;
 average int default 0;
begin
 for i in 1..stop_at loop

   select 
     totalwork - sofar work_remaining
   into units_now
   from v$session_longops l
   where username = 'SCOTT' and sid = 390
   ;
   if units_prev is null then 
     units_prev := units_now;
   else    
     delta := units_prev - units_now;
     total := total + delta;
     average := (total / i);
     
     dbms_output.put_line('Pass: ' || i || ' Prev,Now: ' || units_prev ||
         ',' || units_now || ' Delta: ' || delta || ' Avg: ' || average);
     units_prev := units_now;
   end if;
   
   if i < stop_at then
     dbms_lock.sleep(wait_seconds); 
   else
     dbms_output.put_line('Done: ' || i||  ' Agv: ' || average );
   end if;
 end loop;  
end; 
/

After running the above 5 times every 10 minutes, my average went down to 20.

What operation could possibly be taking so long to precipitate this article? Ah, yes. That would be materializing the order column in Ellucian Banner's curriculum view, sovccur, so we may compare a ranking function to see if we are correctly replicating the order. For just LEARNER records, we're getting through roughly one academic year's worth of order values a day.

Read more articles