When you've got a SQL process that takes a long time, you can project the time that the work will done. Depending on the type of work, you can take two statistic samples of the session. From these samples, you can determine the rate of work. Interpolating the rate against the work remaining, you can find the time that the job will be done. Session statistics you can use to measure progress are: 'table scan rows gotten', 'bytes received via SQL*Net from client', 'bytes received via SQL*Net from dblink', 'bytes sent via SQL*Net from client', 'bytes sent via SQL*Net from dblink' etc. Pick a statistic that relates to the work you are doing.
This script can be used to project the finish time of export of a table. In this script, the number of rows exported will be 2298410.
Rem Determine how much time until a long job is finished. Rem Take two measurements and interpolate the finish time. Rem Parameters: Rem Session id of the process to interpolate Rem Statistic to take a sample of total work to accomplish Rem Start statistic for the session ** must know this beforehand Rem Seconds to take the sample set verify off serveroutput on feedback off; define vsid = 10; define vstat = 'table scan rows gotten'; define vtotal = 2298410; define vstart = 0; define vseconds = 20; alter session set nls_date_format = 'HH24:MI:SS MON-DD'; DECLARE CURSOR c_measure is SELECT value, sysdate FROM v$sesstat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = '&vstat' AND s.sid = &vsid; t1 DATE; -- First Sample Time
t2 DATE; -- Second Sample Time m1 NUMBER; -- First Sample Measurement m2 NUMBER; -- Second Sample Measurement v_rate NUMBER; -- Rate of work per second v_finish INTEGER; -- Remaining seconds til finished BEGIN OPEN c_measure; FETCH c_measure INTO m1, t1; CLOSE c_measure; DBMS_LOCK.SLEEP(&vseconds); -- Wait between samples OPEN c_measure; FETCH c_measure INTO m2, t2; CLOSE c_measure; IF (m2 - m1 > 10) THEN v_rate := (m2 - m1) / ((t2-t1)*24*60*60); v_finish := (&vtotal - &vstart - m2) / v_rate; dbms_output.put_line('Sample time: &vseconds'); dbms_output.put_line('Remaining work: ' ||(&vtotal - &vstart - m2)|| ' &vstat'); dbms_output.put_line('Moving at:'||to_char(v_rate,'9,999,990.9')|| ' per second'); dbms_output.put_line('Finished at... '|| (t2+v_finish/(24*60*60))); ELSE dbms_output.put_line('Nothing is happening'); END IF; END;
Get more tips in minutes! Return to the main page.
About the author: Kenny Smith has been working with Oracle technology on HP servers for over a decade. He specializes in Oracle database architecture, database administration and development. He has presented at numerous Oracle conferences on two continents. He has published many articles describing Oracle solutions and has co-authored "Oracle backup and recovery 101" from Oracle Press.
IOUG: Become a member of the IOUG to access the paper referenced here and a repository of technical content created for Oracle users by Oracle users.