Interpolate when an export/import/refresh will finish

The following is application development tip #1 from the "30 tips in 30 minutes" series, brought to you by the IOUG. Return to the main page for more tips on this topic.

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;