Monitor progress of long running processes

This time I want to share how I use v$session_longops to monitor Oracle long running queries but also how to create your own entries in this view. When monitoring long lasting operation I'm interested what is running now, and what was finished lately. For this purposes I use query:

SELECT sid,
  serial#,
  opname,
  target_desc,
  percent,
  sofar,
  totalwork,
  to_char(start_time,   'hh24:mi:ss') start_time,
  to_char(efin,   'hh24:mi:ss') estimate_fin,
  case when sofar <> totalwork and last_update_time < sysdate-1/10000 then '*' else null end broken
FROM
  (SELECT sid,
     serial#,
     opname,
     target_desc,
     sofar,
     totalwork,
     to_char(CASE
             WHEN totalwork = 0 THEN 1
             ELSE sofar / totalwork
             END *100,    '990') percent,
     start_time,
     last_update_time,
     start_time +((elapsed_seconds + time_remaining) / 86400) efin
   FROM v$session_longops
   ORDER BY  CASE
             WHEN sofar = totalwork 
                THEN 1
                ELSE 0 END,
          efin DESC)
WHERE sofar <> totalwork or rownum <= 20;

It lists all currently running operations and up to twenty lately finished. The most important columns are estimate_finish – it predicts time of process end, and broken – if contains star (*) it is very possible that process was terminated or hung. Constant 1/10000 (about 8 seconds) in comparison

efin < sysdate-1/10000

is to avoid false alerts. If single step of yours process takes more then 8 seconds than you should alter this value to meet your needs. You can find more information on v$session_longops view in Oracle documentation.
Sample output:

Lets take a look how to write your own entries to long operations view. As a samle I'll just use simple loop that for each record in ALL_OBJECTS process will sleep for 0.1 second:

declare
  v_rindex pls_integer;
  v_slno   pls_integer;
begin
  v_rindex := dbms_application_info.set_session_longops_nohint;
  for r_qry in (select t.*, rownum rn, count(*) over () cnt 
                  from ALL_OBJECTS t ) loop
    dbms_application_info.set_session_longops
      ( rindex =>  v_rindex,
        slno   =>  v_slno,
        op_name => 'ALL_OBJECTS processing',
        target  =>  0,
        target_desc => 'ALL_OBJECTS',
        context     => 0,
        sofar       => r_qry.rn,
        totalwork   => r_qry.cnt,
        units       => 'loops'
      );
    dbms_lock.sleep(0.1);
  end loop;
end;

So what is important here:

• rindex – for first call it must be ste to dbms_application_info.set_session_longops_nohint – it means add new row to v$session_longops view
• rownum is used to get row number; it is passed to sofar parameter
• analytical function (count(*) over () cnt) is used to calculate all rows (steps) in that process; it is passed to totalwork parameter
• rindex, slno – should be assigned always to the same variable; it is needed to pass information about row that should be changed

Additionaly you can add when others handler to set progress to 100%, but you do have to do that. Broken flag described above should work fine, and you know if process ended normally or with errors. Full specification of dbms_application_info.set_session_longops

Paweł