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