Monitor and instrument long runing query

With this query (compatible Oracle RAC) using the v$session_longops view, you can view any SQL statement that executes for more than 6 seconds.

select  s.inst_id,
        SQL.SQL_TEXT as "OPERATION",
        to_char(START_TIME, 'dd/mm/yyyy hh24:mi:ss') Start_Time,
        to_char(LAST_UPDATE_TIME, 'dd/mm/yyyy hh24:mi:ss') Last_Update_Time,
        round(TIME_REMAINING/60,1) as "MINUTES_REMAINING",
        round((SOFAR/TOTALWORK) * 100,2) as PCT_DONE
from    gv$session s, 
        gv$sqlarea sql, 
        gv$session_longops op
where     
        s.sid=op.sid
and     s.sql_id = sql.sql_id
and     s.sid = op.sid
and     s.status  = 'ACTIVE'
and     op.totalwork > op.sofar
and     s.sid not in (select distinct sid from gv$mystat where rownum < 2)
order by 4 desc;

The SET_SESSION_LONGOPS procedure of the DBMS_APPLICATION_INFO package can be used to publish your application information about the progress of long operations.
You can insert and update rows in the v$session_longops view:

DECLARE
        rindex    BINARY_INTEGER;
        slno      BINARY_INTEGER;
        totalwork number;
        sofar     number;
        obj       BINARY_INTEGER;
 
      BEGIN
        rindex := dbms_application_info.set_session_longops_nohint;
        sofar := 0;
        totalwork := 10;
 
        WHILE sofar < 10 LOOP
          -- update obj based on sofar
          -- perform task on object target
 
          sofar := sofar + 1;
          dbms_application_info.set_session_longops(rindex, slno,
            "Operation X", obj, 0, sofar, totalwork, "table", "tables");
        END LOOP;
      END;

After instrumentation, v$session_longops view is populated with these informations:

SELECT 
   opname,
   target_desc,
   sofar,
   totalwork,
   time_remaining,
    units
FROM   
   v$session_longops;

Author: Vincent Fenoll, Oracle DBA Montreal
Compatibility: Oracle 10g, 11g, 12c