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, unitsfrom v$session_longops lwhere 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.
withcte_longops as (select totalwork - sofar work_remainingfrom v$session_longops lwhere username = 'SCOTT'and sid = 390),cte_hours as (select (((work_remaining / 22) * 10) / 60) hours_to_gofrom 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') etafrom 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