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ł
