Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

September 3, 2008

MAX_RUN_DURATION attribute of the SCHEDULER to setup a sniper job

Filed under: Oracle How To — H.Tonguç Yılmaz @ 4:36 pm

Many thanks to Ravi R. of OTN Scheduler Forum for the idea, below is the code for a general purpose sniper job that snipes any job that has exceeded its maximum duration.


--
-- this test was done on 11.1 EE
--
-- ensure that the values of are both non-NULL and sufficiently high for your need
select value 
  from v$parameter 
 where name='job_queue_processes';

VALUE
-----
1000

-- if needed dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 10); can be used to set 
select value 
  from all_scheduler_global_attribute
 where ATTRIBUTE_NAME= 'MAX_JOB_SLAVE_PROCESSES';

VALUE
-----
10 

-- create a table for output
create table job_output (a timestamp with time zone, b varchar2(1000));

-- add an event queue subscriber for this user's messages
exec dbms_scheduler.add_event_queue_subscriber('myagent')

-- create a sniper procedure
create or replace procedure sniper_proc
    (message IN sys.scheduler$_event_info) as
begin

    -- if this is not a JOB_OVER_MAX_DUR message, error out
    if message.event_type != 'JOB_OVER_MAX_DUR' then
      raise PROGRAM_ERROR;
    end if;

    -- stop the job
    dbms_scheduler.stop_job('"'||message.object_owner||'"."'||
      message.object_name ||'"');

    -- insert into job output
    insert into job_output values (systimestamp,
     'sniper job sniped '||'"'||message.object_owner||'"."'||
      message.object_name ||'"');
end;
/

-- create a sniper program
begin
    dbms_scheduler.create_program (
        program_name => 'sniper_prog',
        program_action=> 'sniper_proc',
        program_type => 'stored_procedure',
        number_of_arguments => 1,
        enabled => FALSE) ;

    dbms_scheduler.define_metadata_argument ( 'sniper_prog','event_message',1); 
    dbms_scheduler.enable('sniper_prog');
end;
/

-- create a general purpose sniper job to kill any job that has
-- exceeded its max_run_duration
begin
    dbms_scheduler.create_job('sniper_job',
      program_name=>'sniper_prog',
      event_condition =>
        'tab.user_data.event_type = ''JOB_OVER_MAX_DUR''',
      queue_spec =>'sys.scheduler$_event_queue,myagent',
      enabled=>true);
end;
/

-- create two jobs to test the sniper job
begin
    dbms_scheduler.create_job
      ( 'first_job', job_action =>
          'insert into job_output values(systimestamp, ''first job begins'');
           commit; dbms_lock.sleep(120);
           insert into job_output values(systimestamp, ''first job ends'');',
        job_type => 'plsql_block',
        enabled => false ) ;
    dbms_scheduler.set_attribute
      ( 'first_job' , 'max_run_duration' , interval '60' second);

    dbms_scheduler.create_job
      ( 'second_job', job_action =>
          'insert into job_output values(systimestamp, ''second job begins'');
          commit; dbms_lock.sleep(120);
          insert into job_output values(systimestamp, ''second job ends'');',
        job_type => 'plsql_block',
      enabled => false ) ;
    dbms_scheduler.set_attribute
      ( 'second_job' , 'max_run_duration' , interval '60' second);

    dbms_scheduler.enable('first_job');
    dbms_lock.sleep(10);
    dbms_scheduler.enable('second_job');
end;
/

SELECT * FROM job_output ORDER BY a ;

A                                                 B
------------------------------------------------- --------------------------------------------------------------------------------
03/09/2008 11:45:29,585914 +03:00                 first job begins
03/09/2008 11:45:40,019922 +03:00                 second job begins
03/09/2008 11:46:34,226044 +03:00                 sniper job sniped "SH"."FIRST_JOB"
03/09/2008 11:46:44,344798 +03:00                 sniper job sniped "SH"."SECOND_JOB"

One limitation of this is that it only works for jobs in the same schema as the sniper job.

Also there is a similar code example of a job e-mail notification package on the OTN Scheduler page, here.

Advertisements

On Extended Transaction Monitoring Statistics After 10g

Filed under: Oracle 10g New Features,Oracle Performance — H.Tonguç Yılmaz @ 5:13 am

After 10g when a long-running transaction is rolling back and if the process takes more than six seconds this event is recorded in the view V$SESSION_LONGOPS.


-- to estimate when the monitored rollback process will finish
SELECT TIME_REMAINING, SOFAR / TOTALWORK * 100 PCT
  FROM V$SESSION_LONGOPS
 WHERE SID = :sid
   AND OPNAME = 'Transaction Rollback' ;

Also after 10g addition to the user issued rollback statements, transactions recovered by SMON and parallel instance recovery sessions are monitored. You can view historical information about transaction recovery and transaction rollback and you can calculate average rollback duration to estimate better transaction recovery time and set the FAST_START_PARALLEL_ROLLBACK initialization parameter more appropriately to optimize system performance. Historical information is kept in V$FAST_START_TRANSACTIONS until the next instance shutdown.

V$FAST_START_TRANSACTIONS contains information both for transactions that the Oracle server is recovering(the STATE is RECOVERING) and for transactions that the Oracle server has recovered(the STATE is RECOVERED). New columns are added to this view as;

• XID: Transaction ID of this transaction
• PXID: Transaction ID of the parent transaction
• RCVSERVERS: Number of servers working on this transaction including the coordinator server(It can be 1 if only SMON is doing the recovery).

For example this statement can be used to track transaction recovery after instance startup;


SELECT state, undoblocksdone, undoblockstotal, cputime
  FROM v$fast_start_transactions;

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERING 574 1945 16

SQL> /

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERING 1300 1945 34

SQL> /

STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
---------- -------------- --------------- ---------
RECOVERED 1945 1945 65

SQL> SELECT message FROM v$session_longops;

MESSAGE
---------------------------------------------
Transaction Rollback: xid:0x0001.00a.00000812 : 1945 out of 1945 Blocks done

For more reading on this topic please visit the article Oracle Database 10g: The Top 20 Features for DBAs by Arup Nanda on OTN; Week 2 – How Much Longer?: Rollback Monitoring

Create a free website or blog at WordPress.com.