I have a requirement to run several concurrent jobs, and then, only when they have all finished, I want to run another job. Rather than create several stand-alone jobs, I can create a chain of sub-jobs.
- Each step in the chain maps to a program that can invoke either a PL/SQL procedure, a PL/SQL block, a SQL script, or an external program.
- Each step has a rule that includes a condition that determines when it starts. Thus, it can be after one or more other steps have been completed or succeeded.
- A priority can be specified on the program that will determine the order in which programs will be run on the scheduler, all other factors being equal.
- The number of jobs that are permitted to run concurrently can be controlled with a user-defined scheduler resource. The resource is defined as having a number of units. The number of units consumed by a job can be specified in an attribute of a stand-alone job. In a job chain, the resource consumption attribute is applied to the program called from the chain step, rather than the job. Only as many jobs as there are resources available are executed concurrently.
Job Chain Parameters
User-defined parameters can be passed into a stand-alone job, but not (as far as I have been able to find out) into steps in a job chain. Instead job chain metadata, including the job name and sub-name, can be specified as parameters and then application parameters could be looked up for each step in a parameter table.
This naturally leads to a data-driven approach to managing chains, starting with a parameter table containing meta-data from which to create a job chain. Then, when the chain executes, the programs can look up the parameters from the same table, and update other values on it for logging.
Demonstration
- In this example, all the jobs will execute a procedure in a PL/SQL package.
- 10 jobs that will all run for different specified amounts of time.
- I want to run the longest ones first, so they will be given higher priority.
- The jobs will each consume a different number of units of a user-defined resource. Therefore, it will constrain how many jobs can run concurrently.
- A final job will only run when the first 10 jobs have all been completed.
Parameter Table
I will start by creating a parameter table that will be used to create the job chain. It will contain a row for each step in the chain. .
create table test_chain
(seq INTEGER
,chain_name VARCHAR2(128)
,step_name VARCHAR2(24)
,program_name VARCHAR2(128)
,program_action VARCHAR2(128)
,resource_units NUMBER
,priority INTEGER DEFAULT 3 NOT NULL CONSTRAINT test_chain_priority_chk CHECK (priority IN(1,2,3,4,5))
,condition VARCHAR2(4000) DEFAULT 'TRUE' NOT NULL
,end_step VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT test_chain_end_step_chk CHECK (end_step IN('Y','N'))
,seconds number
,begindttm timestamp
,enddttm timestamp
,CONSTRAINT test_chain_uk PRIMARY KEY (chain_name, step_name)
);
The parameter table is populated with the chain steps.
truncate table test_chain;
insert into test_chain
(seq, chain_name, step_name, program_name, program_action, resource_units, condition, priority, seconds)
select 1, 'TEST_CHAIN_1', 'CHAIN_STEP_'||level, 'TEST_PROGRAM_'||level, 'TEST_PROCEDURE'
, level resource_units, 'TRUE' condition, NTILE(5) OVER (order by level desc) priority, 10*level seconds
from dual connect by level <= 10
/
insert into test_chain
(seq, chain_name, step_name, program_name, program_action, seconds)
select 2, 'TEST_CHAIN_1', 'CHAIN_STEP_LAST', 'TEST_PROGRAM_LAST', 'TEST_PROCEDURE', 1
from dual
/
update test_chain c
set condition = (
SELECT LISTAGG(':'||b.step_name||'.state=''SUCCEEDED''',' AND ') WITHIN GROUP (ORDER BY b.step_name)
FROM test_chain b
WHERE b.seq = c.seq-1
and b.chain_name = c.chain_name)
where seq = 2 and chain_name = 'TEST_CHAIN_1'
/
insert into test_chain
(seq, chain_name, step_name, end_step, condition, seconds)
select 3, 'TEST_CHAIN_1', 'CHAIN_STEP_END', 'Y', ':CHAIN_STEP_LAST.state=''SUCCEEDED''', 1
from dual
/
commit;
The chain steps are in 3 sequenced groups.
- 10 concurrent jobs that run first.
- A job that runs after the first 10 jobs have been completed. The initiation criteria are generated with a LISTAGG() function that lists the 10 steps in sequence 1.
- A step that specifies the end of the chain. It is dependent on the job in sequence 2. There is no program for this step.
column seq format 99
column chain_name format a20
column step_name format a20
column program_name format a25
column program_action format a25 wrapped on
column resource_units heading 'Res.|Units' format 99999
column condition format a40
column units format 999
column seconds format 999
select c.*
from test_chain c
where chain_name = 'TEST_CHAIN_1'
order by seq, resource_units; Res.
SEQ CHAIN_NAME STEP_NAME PROGRAM_NAME PROGRAM_ACTION Units PRIORITY CONDITION E SECONDS
--- ------------- ---------------- ------------------ ------------------ ----- ---------- ---------------------------------------- - -------
1 TEST_CHAIN_1 CHAIN_STEP_1 TEST_PROGRAM_1 TEST_PROCEDURE 1 5 TRUE N 10
1 TEST_CHAIN_1 CHAIN_STEP_2 TEST_PROGRAM_2 TEST_PROCEDURE 2 5 TRUE N 20
1 TEST_CHAIN_1 CHAIN_STEP_3 TEST_PROGRAM_3 TEST_PROCEDURE 3 4 TRUE N 30
1 TEST_CHAIN_1 CHAIN_STEP_4 TEST_PROGRAM_4 TEST_PROCEDURE 4 4 TRUE N 40
1 TEST_CHAIN_1 CHAIN_STEP_5 TEST_PROGRAM_5 TEST_PROCEDURE 5 3 TRUE N 50
1 TEST_CHAIN_1 CHAIN_STEP_6 TEST_PROGRAM_6 TEST_PROCEDURE 6 3 TRUE N 60
1 TEST_CHAIN_1 CHAIN_STEP_7 TEST_PROGRAM_7 TEST_PROCEDURE 7 2 TRUE N 70
1 TEST_CHAIN_1 CHAIN_STEP_8 TEST_PROGRAM_8 TEST_PROCEDURE 8 2 TRUE N 80
1 TEST_CHAIN_1 CHAIN_STEP_9 TEST_PROGRAM_9 TEST_PROCEDURE 9 1 TRUE N 90
1 TEST_CHAIN_1 CHAIN_STEP_10 TEST_PROGRAM_10 TEST_PROCEDURE 10 1 TRUE N 100
2 TEST_CHAIN_1 CHAIN_STEP_LAST TEST_PROGRAM_LAST TEST_PROCEDURE 3 :CHAIN_STEP_1.state='SUCCEEDED' AND :CHA N 1
IN_STEP_10.state='SUCCEEDED' AND :CHAIN_
STEP_2.state='SUCCEEDED' AND :CHAIN_STEP
_3.state='SUCCEEDED' AND :CHAIN_STEP_4.s
tate='SUCCEEDED' AND :CHAIN_STEP_5.state
='SUCCEEDED' AND :CHAIN_STEP_6.state='SU
CCEEDED' AND :CHAIN_STEP_7.state='SUCCEE
DED' AND :CHAIN_STEP_8.state='SUCCEEDED'
AND :CHAIN_STEP_9.state='SUCCEEDED'
3 TEST_CHAIN_1 CHAIN_STEP_END 3 :CHAIN_STEP_LAST.state='SUCCEEDED' Y 1
It is only necessary to have multiple programs if you need to execute different procedures, use different priorities, or use different amounts of a resource. In this example, each step has a different program even though they all execute the same procedure because I want to demonstrate the effect of different amounts of resource consumption and different priorities.
Test Procedure
This procedure will be called by the chain steps. The chain step name will be passed to the test procedure as a parameter. The first update statement both updates BEGINDTTM on the parameter table and fetches the number of seconds for which the procedure is to sleep.
create or replace procedure test_procedure
(p_step_name VARCHAR2) as
k_module CONSTANT v$session.module%TYPE := $$PLSQL_UNIT;
l_module v$session.module%TYPE;
l_action v$session.action%TYPE;
l_seconds test_chain.seconds%TYPE;
BEGIN
dbms_application_info.read_module(l_module, l_action);
dbms_application_info.set_module(k_module, p_step_name);
UPDATE test_chain
SET begindttm = SYSTIMESTAMP
WHERE step_name = p_step_name
RETURNING seconds INTO l_seconds;
COMMIT;
dbms_output.put_line(k_module||'.'||p_step_name||':'||l_seconds);
dbms_lock.sleep(l_seconds);
UPDATE test_chain
SET enddttm = SYSTIMESTAMP
WHERE step_name = p_step_name;
COMMIT;
dbms_application_info.set_module(l_module, l_action);
EXCEPTION
WHEN OTHERS THEN
dbms_application_info.set_module(l_module, l_action);
RAISE;
END;
/
Creating the Chain
Then the parameter table is used to create the chain, programs, chain rules, and job that will be executed.
DECLARE
e_scheduler_chain_does_not_exist EXCEPTION;
PRAGMA exception_init(e_scheduler_chain_does_not_exist,-23308);
e_scheduler_job_does_not_exist EXCEPTION;
PRAGMA exception_init(e_scheduler_job_does_not_exist,-27475);
e_scheduler_object_does_not_exist EXCEPTION;
PRAGMA exception_init(e_scheduler_object_does_not_exist,-27476);
e_scheduler_object_already_exists EXCEPTION;
PRAGMA exception_init(e_scheduler_object_already_exists,-27477);
l_job_suffix CONSTANT VARCHAR2(10) := '_JOB';
l_resource_suffix CONSTANT VARCHAR2(10) := '_RESOURCE';
BEGIN
FOR i IN (SELECT DISTINCT chain_name FROM test_chain) LOOP
BEGIN --drop resource if already present
DBMS_SCHEDULER.drop_resource (resource_name => i.chain_name||l_resource_suffix);
EXCEPTION WHEN e_scheduler_object_does_not_exist THEN NULL;
END;
DBMS_SCHEDULER.create_resource ( --recreate resource
resource_name => i.chain_name||l_resource_suffix,
units => 10,
status => 'ENFORCE_CONSTRAINTS', -- Default
constraint_level => 'JOB_LEVEL'); -- Default
BEGIN --drop scheduler job if already present
DBMS_SCHEDULER.drop_job(job_name => i.chain_name||l_job_suffix);
EXCEPTION WHEN e_scheduler_job_does_not_exist THEN NULL;
END;
BEGIN --drop chain if already present
DBMS_SCHEDULER.drop_chain (chain_name => i.chain_name, force=>TRUE);
EXCEPTION WHEN e_scheduler_chain_does_not_exist THEN NULL;
END;
DBMS_SCHEDULER.create_chain ( --recreate chain
chain_name => i.chain_name,
rule_set_name => NULL,
evaluation_interval => NULL);
END LOOP;
FOR i IN (
select c.* from test_chain c
ORDER BY seq, priority, resource_units desc
) LOOP
dbms_output.put_line(i.chain_name||', Step:'||i.step_name||', Condition:'||i.condition);
IF i.program_name IS NOT NULL THEN
BEGIN
DBMS_SCHEDULER.create_program ( --create program to call stored procedure
program_name => i.program_name,
program_type => 'STORED_PROCEDURE',
program_action => i.program_action,
number_of_arguments => 1,
enabled => FALSE,
comments => 'Program for chain:'||i.chain_name||', step:'||i.step_name);
DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT( --pass job_subname as first parameter
program_name => i.program_name,
metadata_attribute => 'job_subname',
argument_position => 1);
DBMS_SCHEDULER.set_attribute ( --apply priority to program
name => i.program_name,
attribute => 'job_priority',
value => i.priority);
DBMS_SCHEDULER.set_resource_constraint ( --apply resource consumption constraint to program
object_name => i.program_name, --cannot go on step
resource_name => i.chain_name||l_resource_suffix,
units => i.resource_units);
dbms_scheduler.enable(i.program_name);
dbms_output.put_line(i.chain_name||', Step:'||i.step_name||', Program:'||i.program_name);
EXCEPTION WHEN e_scheduler_object_already_exists THEN NULL;
END;
DBMS_SCHEDULER.define_chain_step ( --create chain step to call program
chain_name => i.chain_name,
step_name => i.step_name,
program_name => i.program_name);
END IF;
IF i.end_step = 'Y' THEN --if last step in chain
DBMS_SCHEDULER.define_chain_rule ( -- create job chain end step
chain_name => i.chain_name,
condition => i.condition,
action => 'END',
rule_name => i.step_name,
comments => 'End of chain '||i.chain_name);
DBMS_SCHEDULER.enable (i.chain_name); --enable the chain
dbms_scheduler.create_job ( --create a job to execute the chain once
job_name=> i.chain_name||l_job_suffix,
job_type=> 'CHAIN',
job_action=> i.chain_name,
start_date=> sysdate,
enabled=> FALSE);
ELSE --otherwise create an ordinary job rule for each step
DBMS_SCHEDULER.define_chain_rule (
chain_name => i.chain_name,
condition => i.condition,
action => 'START "'||i.step_name||'"',
rule_name => i.step_name,
comments => 'Sequence '||i.seq);
END IF;
END LOOP;
END;
/
TEST_CHAIN_1, Step:CHAIN_STEP_10, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_10, Program:TEST_PROGRAM_10
TEST_CHAIN_1, Step:CHAIN_STEP_9, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_9, Program:TEST_PROGRAM_9
TEST_CHAIN_1, Step:CHAIN_STEP_8, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_8, Program:TEST_PROGRAM_8
TEST_CHAIN_1, Step:CHAIN_STEP_7, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_7, Program:TEST_PROGRAM_7
TEST_CHAIN_1, Step:CHAIN_STEP_6, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_6, Program:TEST_PROGRAM_6
TEST_CHAIN_1, Step:CHAIN_STEP_5, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_5, Program:TEST_PROGRAM_5
TEST_CHAIN_1, Step:CHAIN_STEP_4, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_4, Program:TEST_PROGRAM_4
TEST_CHAIN_1, Step:CHAIN_STEP_3, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_3, Program:TEST_PROGRAM_3
TEST_CHAIN_1, Step:CHAIN_STEP_2, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_2, Program:TEST_PROGRAM_2
TEST_CHAIN_1, Step:CHAIN_STEP_1, Condition:TRUE
TEST_CHAIN_1, Step:CHAIN_STEP_1, Program:TEST_PROGRAM_1
TEST_CHAIN_1, Step:CHAIN_STEP_LAST, Condition::CHAIN_STEP_1.state='SUCCEEDED' AND :CHAIN_STEP_10.state='SUCCEEDED' AND :CHAIN_STEP_2.state='SUCCEEDED'
AND :CHAIN_STEP_3.state='SUCCEEDED' AND :CHAIN_STEP_4.state='SUCCEEDED' AND :CHAIN_STEP_5.state='SUCCEEDED' AND :CHAIN_STEP_6.state='SUCCEEDED'
AND :CHAIN_STEP_7.state='SUCCEEDED' AND :CHAIN_STEP_8.state='SUCCEEDED' AND :CHAIN_STEP_9.state='SUCCEEDED'
TEST_CHAIN_1, Step:CHAIN_STEP_LAST, Program:TEST_PROGRAM_LAST
TEST_CHAIN_1, Step:CHAIN_STEP_END, Condition::CHAIN_STEP_LAST.state='SUCCEEDED'
PL/SQL procedure successfully completed.
Exploring the Chain
Various views are available to see how the chain is defined.
select * from all_scheduler_resources WHERE resource_name like 'TEST_CHAIN%'
Jobs
Resource Run
OWNER RESOURCE_NAME STATUS Units UNITS_USED Count COMMENTS
---------- -------------------------------- -------------------- -------- ---------- ----- --------------------
SYSADM TEST_CHAIN_1_RESOURCE ENFORCE_CONSTRAINTS 10 0 0
SELECT owner,chain_name,rule_set_owner,rule_set_name,number_of_rules,number_of_steps,enabled,comments
FROM all_scheduler_chains
WHERE chain_name like 'TEST_CHAIN%';
Rule Set
OWNER CHAIN_NAME Owner RULE_SET_NAME NUMBER_OF_RULES NUMBER_OF_STEPS ENABLED COMMENTS
---------- -------------------- ---------- --------------- --------------- --------------- ------- ----------------------------------------
SYSADM TEST_CHAIN_1 SYSADM SCHED_RULESET$7 12 11 TRUE
SELECT owner, program_name, program_type, program_action, number_of_arguments, enabled, priority, weight, has_Constraints, comments
FROM all_SCHEDULER_PROGRAMS
WHERE PROGRAM_NAME LIKE 'TEST_PROGRAM%';
Num Has
OWNER PROGRAM_NAME PROGRAM_TYPE PROGRAM_ACTION Args ENABLED Prio Wgt Const. COMMENTS
---------- -------------------- ---------------- --------------- ---- ------- ---- --- ------ ------------------------------------------------------------
SYSADM TEST_PROGRAM_10 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 1 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_10
SYSADM TEST_PROGRAM_9 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 1 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_9
SYSADM TEST_PROGRAM_8 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 2 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_8
SYSADM TEST_PROGRAM_7 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 2 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_7
SYSADM TEST_PROGRAM_6 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 3 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_6
SYSADM TEST_PROGRAM_5 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 3 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_5
SYSADM TEST_PROGRAM_4 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 4 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_4
SYSADM TEST_PROGRAM_3 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 4 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_3
SYSADM TEST_PROGRAM_2 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 5 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_2
SYSADM TEST_PROGRAM_1 STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 5 1 TRUE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_1
SYSADM TEST_PROGRAM_LAST STORED_PROCEDURE TEST_PROCEDURE 1 TRUE 3 1 FALSE Program for chain:TEST_CHAIN_1, step:CHAIN_STEP_LAST
SELECT owner, chain_name, step_name, program_owner, program_name, step_type
FROM all_scheduler_chain_steps
WHERE chain_name like 'TEST_CHAIN%'
ORDER BY owner, chain_name, step_name;
Program
OWNER CHAIN_NAME STEP_NAME Owner PROGRAM_NAME STEP_TYPE
---------- -------------------- ------------------------- ---------- -------------------- ----------
SYSADM TEST_CHAIN_1 CHAIN_STEP_1 SYSADM TEST_PROGRAM_1 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_10 SYSADM TEST_PROGRAM_10 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_2 SYSADM TEST_PROGRAM_2 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_3 SYSADM TEST_PROGRAM_3 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_4 SYSADM TEST_PROGRAM_4 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_5 SYSADM TEST_PROGRAM_5 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_6 SYSADM TEST_PROGRAM_6 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_7 SYSADM TEST_PROGRAM_7 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_8 SYSADM TEST_PROGRAM_8 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_9 SYSADM TEST_PROGRAM_9 PROGRAM
SYSADM TEST_CHAIN_1 CHAIN_STEP_LAST SYSADM TEST_PROGRAM_LAST PROGRAM
SELECT owner,chain_name,rule_owner,rule_name,condition,action,comments
FROM all_scheduler_chain_rules
WHERE chain_name like 'TEST_CHAIN%'
ORDER BY owner, chain_name, rule_owner, rule_name;
Rule
OWNER CHAIN_NAME Owner RULE_NAME CONDITION ACTION COMMENTS
---------- ------------- ---------- ------------------ -------------------------------------------------- ------------------------------ ------------------------------
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_1 TRUE START "CHAIN_STEP_1" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_10 TRUE START "CHAIN_STEP_10" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_2 TRUE START "CHAIN_STEP_2" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_3 TRUE START "CHAIN_STEP_3" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_4 TRUE START "CHAIN_STEP_4" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_5 TRUE START "CHAIN_STEP_5" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_6 TRUE START "CHAIN_STEP_6" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_7 TRUE START "CHAIN_STEP_7" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_8 TRUE START "CHAIN_STEP_8" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_9 TRUE START "CHAIN_STEP_9" Sequence 1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_END :CHAIN_STEP_LAST.state='SUCCEEDED' END End of chain TEST_CHAIN_1
SYSADM TEST_CHAIN_1 SYSADM CHAIN_STEP_LAST :CHAIN_STEP_1.state='SUCCEEDED' AND :CHAIN_STEP_10 START "CHAIN_STEP_LAST" Sequence 2
.state='SUCCEEDED' AND :CHAIN_STEP_2.state='SUCCEE
DED' AND :CHAIN_STEP_3.state='SUCCEEDED' AND :CHAI
N_STEP_4.state='SUCCEEDED' AND :CHAIN_STEP_5.state
='SUCCEEDED' AND :CHAIN_STEP_6.state='SUCCEEDED' A
ND :CHAIN_STEP_7.state='SUCCEEDED' AND :CHAIN_STEP
.state='SUCCEEDED' AND :CHAIN_STEP_9.state='SUCC
EEDED'
Executing the Chain
Simply enable the job to execute the chain. The job created by this PL/SQL will only execute the chain once because by default it will automatically drop after it completes.
exec DBMS_SCHEDULER.enable ('test_chain_1_job');
Monitoring the Chain
Oracle also provides views to monitor running jobs and chains.
ALL_SCHEDULER_RUNNING_CHAINS reports the current status of each step in the chain.
SELECT owner,job_name,chain_owner,chain_name,step_name,state
FROM all_scheduler_running_chains ORDER BY owner, job_name, chain_name, step_name;
Chain
OWNER JOB_NAME Owner CHAIN_NAME STEP_NAME STATE
---------- ------------------------ ---------- -------------------- ------------------------- ---------------
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_1 SUCCEEDED
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_10 RUNNING
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_2 SUCCEEDED
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_3 SUCCEEDED
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_4 SUCCEEDED
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_5 RUNNING
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_6 SUCCEEDED
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_7 RUNNING
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_8 RUNNING
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_9 SUCCEEDED
SYSADM TEST_CHAIN_1_JOB SYSADM TEST_CHAIN_1 CHAIN_STEP_LAST NOT_STARTED
You can also see each completed job and sub-job on
ALL_SCHEDULER_RUN_DETAILS.
select log_id, job_name, job_subname, req_start_date, actual_start_date, log_date, run_duration, output
from all_scheduler_job_run_details
where job_name like 'TEST_CHAIN%'
AND log_date > sysdate-…
order by actual_start_date;
LOG_ID JOB_NAME JOB_SUBNAME REQ_START_DATE ACTUAL_START_DATE LOG_DATE RUN_DURATION OUTPUT
---------- ------------------------ ------------------------ ------------------------------ ------------------------------ ------------------------------ --------------- ----------------------------------------
7942016 TEST_CHAIN_1_JOB 26/12/2023 15:54:15.720 +00:00 26/12/2023 15:54:18.970 +00:00 26/12/2023 11:03:05.901 -05:00 +000 00:08:47
7941800 TEST_CHAIN_1_JOB CHAIN_STEP_9 26/12/2023 15:54:19.279 +00:00 26/12/2023 15:54:19.494 +00:00 26/12/2023 10:55:49.599 -05:00 +000 00:01:30 TEST_PROCEDURE.CHAIN_STEP_9:90
7941892 TEST_CHAIN_1_JOB CHAIN_STEP_4 26/12/2023 15:54:19.633 +00:00 26/12/2023 15:56:00.613 +00:00 26/12/2023 10:56:40.671 -05:00 +000 00:00:40 TEST_PROCEDURE.CHAIN_STEP_4:40
7941894 TEST_CHAIN_1_JOB CHAIN_STEP_2 26/12/2023 15:54:19.651 +00:00 26/12/2023 15:56:00.615 +00:00 26/12/2023 10:56:20.832 -05:00 +000 00:00:20 TEST_PROCEDURE.CHAIN_STEP_2:20
7941906 TEST_CHAIN_1_JOB CHAIN_STEP_3 26/12/2023 15:54:19.639 +00:00 26/12/2023 15:56:05.946 +00:00 26/12/2023 10:56:36.206 -05:00 +000 00:00:30 TEST_PROCEDURE.CHAIN_STEP_3:30
7941952 TEST_CHAIN_1_JOB CHAIN_STEP_6 26/12/2023 15:54:19.620 +00:00 26/12/2023 15:56:36.438 +00:00 26/12/2023 10:57:36.608 -05:00 +000 00:01:00 TEST_PROCEDURE.CHAIN_STEP_6:60
7941940 TEST_CHAIN_1_JOB CHAIN_STEP_10 26/12/2023 15:54:19.261 +00:00 26/12/2023 15:57:37.626 +00:00 26/12/2023 10:59:17.691 -05:00 +000 00:01:40 TEST_PROCEDURE.CHAIN_STEP_10:100
7942000 TEST_CHAIN_1_JOB CHAIN_STEP_8 26/12/2023 15:54:19.388 +00:00 26/12/2023 15:59:18.696 +00:00 26/12/2023 11:00:38.840 -05:00 +000 00:01:20 TEST_PROCEDURE.CHAIN_STEP_8:80
7942032 TEST_CHAIN_1_JOB CHAIN_STEP_5 26/12/2023 15:54:19.628 +00:00 26/12/2023 16:00:48.614 +00:00 26/12/2023 11:01:38.783 -05:00 +000 00:00:50 TEST_PROCEDURE.CHAIN_STEP_5:50
7942036 TEST_CHAIN_1_JOB CHAIN_STEP_7 26/12/2023 15:54:19.500 +00:00 26/12/2023 16:01:44.374 +00:00 26/12/2023 11:02:54.432 -05:00 +000 00:01:10 TEST_PROCEDURE.CHAIN_STEP_7:70
7942014 TEST_CHAIN_1_JOB CHAIN_STEP_LAST 26/12/2023 16:02:54.564 +00:00 26/12/2023 16:02:59.714 +00:00 26/12/2023 11:03:00.729 -05:00 +000 00:00:01 TEST_PROCEDURE.CHAIN_STEP_LAST:1
The start and end time of each step are also recorded on the parameter table by TEST_PROCEDURE
select * from test_chain
where chain_name = 'TEST_CHAIN_1'
order by seq, resource_units;
SEQ CHAIN_NAME STEP_NAME PROGRAM_NAME PROGRAM_ACTION Units Prio CONDITION END Secs. BEGINDTTM ENDDTTM
--- --------------- -------------------- ----------------- -------------- ----- ---- -------------------------------------------------- --- ----- ------------------------ ------------------------
1 TEST_CHAIN_1 CHAIN_STEP_1 TEST_PROGRAM_1 TEST_PROCEDURE 1 5 TRUE N 10 26/12/2023 10:54:19.737 26/12/2023 10:54:29.791
1 TEST_CHAIN_1 CHAIN_STEP_2 TEST_PROGRAM_2 TEST_PROCEDURE 2 5 TRUE N 20 26/12/2023 10:56:00.636 26/12/2023 10:56:20.828
1 TEST_CHAIN_1 CHAIN_STEP_3 TEST_PROGRAM_3 TEST_PROCEDURE 3 4 TRUE N 30 26/12/2023 10:56:05.960 26/12/2023 10:56:36.188
1 TEST_CHAIN_1 CHAIN_STEP_4 TEST_PROGRAM_4 TEST_PROCEDURE 4 4 TRUE N 40 26/12/2023 10:56:00.626 26/12/2023 10:56:40.667
1 TEST_CHAIN_1 CHAIN_STEP_5 TEST_PROGRAM_5 TEST_PROCEDURE 5 3 TRUE N 50 26/12/2023 11:00:48.621 26/12/2023 11:01:38.779
1 TEST_CHAIN_1 CHAIN_STEP_6 TEST_PROGRAM_6 TEST_PROCEDURE 6 3 TRUE N 60 26/12/2023 10:56:36.443 26/12/2023 10:57:36.604
1 TEST_CHAIN_1 CHAIN_STEP_7 TEST_PROGRAM_7 TEST_PROCEDURE 7 2 TRUE N 70 26/12/2023 11:01:44.378 26/12/2023 11:02:54.428
1 TEST_CHAIN_1 CHAIN_STEP_8 TEST_PROGRAM_8 TEST_PROCEDURE 8 2 TRUE N 80 26/12/2023 10:59:18.702 26/12/2023 11:00:38.837
1 TEST_CHAIN_1 CHAIN_STEP_9 TEST_PROGRAM_9 TEST_PROCEDURE 9 1 TRUE N 90 26/12/2023 10:54:19.546 26/12/2023 10:55:49.596
1 TEST_CHAIN_1 CHAIN_STEP_10 TEST_PROGRAM_10 TEST_PROCEDURE 10 1 TRUE N 100 26/12/2023 10:57:37.640 26/12/2023 10:59:17.687
2 TEST_CHAIN_1 CHAIN_STEP_LAST TEST_PROGRAM_LAST TEST_PROCEDURE 3 :CHAIN_STEP_1.state='SUCCEEDED' AND :CHAIN_STEP_10 N 1 26/12/2023 11:02:59.722 26/12/2023 11:03:00.725
.state='SUCCEEDED' AND :CHAIN_STEP_2.state='SUCCEE
DED' AND :CHAIN_STEP_3.state='SUCCEEDED' AND :CHAI
N_STEP_4.state='SUCCEEDED' AND :CHAIN_STEP_5.state
='SUCCEEDED' AND :CHAIN_STEP_6.state='SUCCEEDED' A
ND :CHAIN_STEP_7.state='SUCCEEDED' AND :CHAIN_STEP
_8.state='SUCCEEDED' AND :CHAIN_STEP_9.state='SUCC
EEDED'
3 TEST_CHAIN_1 CHAIN_STEP_END 3 :CHAIN_STEP_LAST.state='SUCCEEDED' Y 1
Acknowledgments
@go-faster.co.uk on BlueSky
Posts
