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
- Log in to post comments