Search Oracle Blogs
Wednesday, March 28, 2007
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:
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:
Paweł
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/10000is 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
Paweł
Subscribe to:
Post Comments (Atom)
1 comment:
Thx! Very Usefull
Post a Comment