Just because the execution plan says INMEMORY, it doesn't mean it is using In-Memory

Parallel Query

If you are using RAC, and you have in-memory objects populated across nodes (i.e. distribution by ROWID range) or you have objects populated in only 1 node (i.e. distribution by partition or sub-partition) then you need to use parallel query to access data populated on a node to which the query is not connected. 

• There is no cache fusion with Database In-Memory.  Oracle does not ship In-Memory Compression Units (IMCUs) across the RAC interconnect.
• Similarly, if you have set PARALLEL_FORCE_LOCAL=TRUE the parallel query will not be able to access the remote nodes.

In-memory improves performance by avoiding physical I/O, but the reduction in CPU consumption can be more significant.  In the cloud, this can save money by reducing your cloud subscription costs.  However, parallel query can be a brutal way of using CPU to complete a query faster.  It often increases total CPU consumption, thus negating some of the benefits of in-memory.

Options:

A query that is not executing in parallel will only be able to access objects in the local in-memory store.  You can ensure that a segment is stored in the in-memory store on every RAC node by specifying DUPLICATE ALL.  Parallel queries will also use the local in-memory store.  

• This option can improve performance but the in-memory stores consume more memory.  On a 2-node RAC database, it doubles the memory consumption of In-Memory.
• The DUPLICATE option is only available on Exadata.  On other platforms, it is ignored (see also Oracle Database In-Memory on RAC - Part 3).

Alternatively, you can use database services to create node affinity.  

• A process can connect using a database service that specifies a specific node or nodes.  
• Parallel queries can be restricted to specific nodes by setting PARALLEL_INSTANCE_GROUP to use a service (see also Oracle Database In-Memory on RAC - Part 2).
• In-memory segments can be placed in the in-memory store on specific nodes by distributing them with a specific service (see also How to control where objects are populated into [In-]memory on RAC).
• You may prefer to create different services for the query processes and in-memory population processes.  In the case of node failure, you probably want the query process connection to fail over to another node.  However, you may not want that to happen for in-memory distribution processes because of the additional memory overhead.

Otherwise, on a 2-node RAC, a non-parallel query has a 50% chance of finding the segment in the in-memory store because it has a 50% chance of connecting to the node where it is stored!

Is It Using In Memory?

I am going to demonstrate this using a table with 2 partitions.

CREATE TABLE t (a number, b number, c VARCHAR2(1000)) PARTITION BY RANGE (b)
(partition t1 VALUES LESS THAN(50)       
,partition t2 VALUES LESS THAN(MAXVALUE)
) INMEMORY;
INSERT INTO t SELECT level, MOD(level,100),  RPAD(TO_CHAR(TO_DATE(level,'j'),'Jsp'),100,'.')
FROM DUAL CONNECT BY LEVEL <= 1e5;
commit;

Serial Query

I am going to generate execute plans for two similar queries that each query different partitions of a table. The execution plans have the same plan hash value. The only difference is that the first query accesses only the first partition, and the second query only accesses the second partition.  

Both plans claim they are doing an INMEMORY full scan of the table.  However, this is only a statement of intent.

explain plan for SELECT sum(a), sum(b), count(*) FROM t WHERE b=42;
…
Plan hash value: 2993254470
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    26 |    11   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE              |      |     1 |    26 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE     |      |   942 | 24492 |    11   (0)| 00:00:01 |     1 |     1 |
|*  3 |    TABLE ACCESS INMEMORY FULL| T    |   942 | 24492 |    11   (0)| 00:00:01 |     1 |     1 |
-----------------------------------------------------------------------------------------------------

explain plan for SELECT sum(a), sum(b), count(*) FROM t WHERE b=56;
…
Plan hash value: 2993254470
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    26 |    11   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE              |      |     1 |    26 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE     |      |   926 | 24076 |    11   (0)| 00:00:01 |     2 |     2 |
|*  3 |    TABLE ACCESS INMEMORY FULL| T    |   926 | 24076 |    11   (0)| 00:00:01 |     2 |     2 |
-----------------------------------------------------------------------------------------------------

Oracle, distributes the partitions across the in-memory stores in RAC nodes.  In my case, the first partition is on instance 1, and the second partition is on instance 2.

select inst_id, owner, segment_name, partition_name, inmemory_size, bytes, bytes_not_populated, populate_status, inmemory_duplicate
from gv$im_segments where segment_name = 'T' order by inst_id;

   INST_ID OWNER      SEGMENT_NAME PARTITION_NAME INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED POPULATE_STAT INMEMORY_DUPL
---------- ---------- ------------ -------------- ------------- ---------- ------------------- ------------- -------------
         1 SYSADM     T            T1                   6422528    8241152                   0 COMPLETED     NO DUPLICATE
         2 SYSADM     T            T2                   6422528    8241152                   0 COMPLETED     NO DUPLICATE

I will run the queries, capturing the session statistics to a temporary table. I use the Oracle-delivered global temporary table plan_table so that I don't have to create my own table.

delete from plan_table;
insert into plan_table (statement_id, plan_id, id, cost, parent_id) select '_', s.* from v$mystat s;

SELECT sum(a), sum(b), count(*) FROM t WHERE b=42;

insert into plan_table (statement_id, plan_id, id, cost, parent_id) select 'A', s.* from v$mystat s;

SELECT sum(a), sum(b), count(*) FROM t WHERE b=56;

insert into plan_table (statement_id, plan_id, id, cost, parent_id) select 'B', s.* from v$mystat s;

Then I can simply query where the IM statistics are different.

with x (scenario, sid, statistic#, value) as (select statement_id, plan_id, id, cost from plan_table)
select x.statistic#, n.name 
, a.value-x.value diff_a
, b.value-a.value diff_b
from v$statname n, x, x a, x b
where x.scenario = '_'
and x.sid = a.sid and x.statistic# = a.statistic# and a.scenario = 'A'
and x.sid = b.sid and x.statistic# = b.statistic# and b.scenario = 'B'
and (x.value < a.value OR a.value < b.value)
and n.statistic# = x.statistic#
and n.name like 'IM %' and not n.name like 'IM %populate%'
order by x.statistic#;

I only got an in-memory query on instance 2. On instance 1, there is a single IM scan segments disk operation. This is the 'number of times a segment marked for in-memory was accessed entirely from the buffer cache/direct read' (see Popular Statistics with Database In-Memory). This indicates that there was no in-memory query. 

STATISTIC# NAME                                                   DIFF_A     DIFF_B
---------- -------------------------------------------------- ---------- ----------
       772 IM scan CUs no cleanout                                     0          1
       802 IM scan CUs current                                         0          1
       830 IM scan CUs readlist creation accumulated time              0          2
       832 IM scan CUs readlist creation number                        0          1
       838 IM scan delta - only base scan                              0          1
      1376 IM scan CUs pcode aggregation pushdown                      0          3
      1377 IM scan rows pcode aggregated                               0       1000
      1379 IM scan CUs pcode pred evaled                               0          1
      1385 IM scan dict engine results reused                          0          3
      1480 IM scan CUs memcompress for query low                       0          1
      1493 IM scan segments disk                                       1          0
      1494 IM scan bytes in-memory                                     0    5940559
      1495 IM scan bytes uncompressed                                  0    5444950
      1496 IM scan CUs columns accessed                                0          2
      1498 IM scan CUs columns theoretical max                         0          3
      1505 IM scan rows                                                0      50000
      1506 IM simd compare calls                                       0          3
      1512 IM simd decode unpack calls                                 0          6
      1513 IM simd decode symbol calls                                 0          2
      1520 IM simd decode unpack selective calls                       0          6
      1527 IM scan rows valid                                          0      50000
      1533 IM scan rows projected                                      0          1
      1538 IM scan CUs split pieces                                    0          1
      1571 IM scan CUs predicates received                             0          1
      1572 IM scan CUs predicates applied                              0          1
      1577 IM scan segments minmax eligible                            0          1
      1611 IM SubCU-MM CUs Examined                                    0          1

Parallel Query 

I will repeat the test, but use a parallel hint to enable parallel query.

SELECT /*+PARALLEL*/ sum(a), sum(b), count(*) FROM t WHERE b=42;

Now, I get a parallel execution plan

Plan hash value: 943991435
 
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |     1 |    26 |     6   (0)| 00:00:01 |       |       |        |      |            |
|   1 |  SORT AGGREGATE                 |          |     1 |    26 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR                |          |       |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000 |     1 |    26 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |          |     1 |    26 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |          |   942 | 24492 |     6   (0)| 00:00:01 |     1 |     1 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS INMEMORY FULL| T        |   942 | 24492 |     6   (0)| 00:00:01 |     1 |     1 |  Q1,00 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------

The IM statistics show that both the queries performed an in-memory query.

STATISTIC# NAME                                                   DIFF_A     DIFF_B
---------- -------------------------------------------------- ---------- ----------
       772 IM scan CUs no cleanout                                     3          3
       802 IM scan CUs current                                         3          3
       830 IM scan CUs readlist creation accumulated time              4          4
       832 IM scan CUs readlist creation number                        3          3
       838 IM scan delta - only base scan                              3          3
      1376 IM scan CUs pcode aggregation pushdown                      9          9
      1377 IM scan rows pcode aggregated                            1000       1000
      1379 IM scan CUs pcode pred evaled                               3          3
      1385 IM scan dict engine results reused                          9          9
      1480 IM scan CUs memcompress for query low                       3          3
      1494 IM scan bytes in-memory                              17819283   17821701
      1495 IM scan bytes uncompressed                           16328826   16334850
      1496 IM scan CUs columns accessed                                6          6
      1498 IM scan CUs columns theoretical max                         9          9
      1505 IM scan rows                                           150000     150000
      1506 IM simd compare calls                                       9          9
      1512 IM simd decode unpack calls                                18         18
      1513 IM simd decode symbol calls                                 6          6
      1520 IM simd decode unpack selective calls                      18         18
      1527 IM scan rows valid                                      50000      50000
      1529 IM scan rows range excluded                            100000     100000
      1533 IM scan rows projected                                      3          3
      1538 IM scan CUs split pieces                                    6          3
      1571 IM scan CUs predicates received                             3          3
      1572 IM scan CUs predicates applied                              3          3
      1577 IM scan segments minmax eligible                            3          3
      1611 IM SubCU-MM CUs Examined                                    3          3

Duplicate In-Memory Store 

This time, I will repeat the test with a duplicate in-memory store. The DUPLICATE option stores the segment in the in-memory store on one other RAC node, the DUPLICATE ALL option stores it on all RAC nodes. On a 2-node RAC they come to the same thing.

CREATE TABLE t (a number, b number, c VARCHAR2(1000)) PARTITION BY RANGE (b)
(partition t1 VALUES LESS THAN(50)       
,partition t2 VALUES LESS THAN(MAXVALUE)
) INMEMORY DUPLICATE ALL;

Now, both partitions are stored on both instances.

select inst_id, owner, segment_name, partition_name, inmemory_size, bytes, bytes_not_populated, populate_status, inmemory_duplicate
from gv$im_segments where segment_name = 'T' order by inst_id, segment_name, partition_name;

   INST_ID OWNER      SEGMENT_NAME PARTITION_NAME INMEMORY_SIZE      BYTES BYTES_NOT_POPULATED POPULATE_STAT INMEMORY_DUPL
---------- ---------- ------------ -------------- ------------- ---------- ------------------- ------------- -------------
         1 SYSADM     T            T1                   6422528    8241152                   0 COMPLETED     DUPLICATE
         1 SYSADM     T            T2                   6422528    8241152                   0 COMPLETED     DUPLICATE
         2 SYSADM     T            T1                   6422528    8241152                   0 COMPLETED     DUPLICATE
         2 SYSADM     T            T2                   9568256    8241152                   0 COMPLETED     DUPLICATE 

I will return to the original queries without the parallel hints

SELECT sum(a), sum(b), count(*) FROM t WHERE b=42;
SELECT sum(a), sum(b), count(*) FROM t WHERE b=56;

The in-memory statistics are the same for both queries, indicating that an in-memory query was successfully performed for both partitions because both partitions are stored in in-memory on both instances.

STATISTIC# NAME                                                   DIFF_A     DIFF_B
---------- -------------------------------------------------- ---------- ----------
       772 IM scan CUs no cleanout                                     1          1
       802 IM scan CUs current                                         1          1
       830 IM scan CUs readlist creation accumulated time              3          2
       832 IM scan CUs readlist creation number                        1          1
       838 IM scan delta - only base scan                              1          1
      1376 IM scan CUs pcode aggregation pushdown                      3          3
      1377 IM scan rows pcode aggregated                            1000       1000
      1379 IM scan CUs pcode pred evaled                               1          1
      1385 IM scan dict engine results reused                          3          3
      1480 IM scan CUs memcompress for query low                       1          1
      1494 IM scan bytes in-memory                               5939777    5940563
      1495 IM scan bytes uncompressed                            5442942    5444950
      1496 IM scan CUs columns accessed                                2          2
      1498 IM scan CUs columns theoretical max                         3          3
      1505 IM scan rows                                            50000      50000
      1506 IM simd compare calls                                       3          3
      1512 IM simd decode unpack calls                                 6          6
      1513 IM simd decode symbol calls                                 2          2
      1520 IM simd decode unpack selective calls                       6          6
      1527 IM scan rows valid                                      50000      50000
      1533 IM scan rows projected                                      1          1
      1538 IM scan CUs split pieces                                    2          2
      1571 IM scan CUs predicates received                             1          1
      1572 IM scan CUs predicates applied                              1          1
      1577 IM scan segments minmax eligible                            1          1
      1611 IM SubCU-MM CUs Examined                                    1          1

TL;DR 

The presence of an in-memory operation in an execution plan does not mean that the statement is definitely using in-memory. Rather, it means that it will use an in-memory query if it finds the segment in the in-memory store, and that content is up to date. 

Look at the session level statistics to determine whether the query really did use in-memory as I have demonstrated in this blog, or a SQL Monitor active report (see Oracle Database In-Memory on RAC - Part 1 (revised)). 

Parallel query is used to access an object stored in in-memory on a different node remote to where the session is connected. If the query is not run in parallel it will not be able to access it. This will be indicated by the ' IM scan segments disk' statistics. Alternatives are to duplicate the in-memory store on Exadata or to use services to create node affinity.

My thanks to Andy Rivenes for the initial comment that sent me off into this subject, and to the various articles that he and Maria Colgan have posted on Oracle Database In-Memory blog that I have linked in this note.

Job Chains

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. 

1. 10 concurrent jobs that run first.
2. 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.
3. 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 

All of this can be worked out from the Oracle documentation, but I have found these pages very helpful:

Tim Hall's Oracle-Base.com

Scheduler Enhancements in Oracle 10g Database Release 2: Job Chains

Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 2 (12.2): Scheduler Resource Queues

Oracle Support Note 1272728.1: Is It Possible To Assign Chain Parameters To A Scheduler Job?

AskTOM

Connor McDonald: How to start a job on two conditions with DBMS_SCHEDULER : another job has finished and we are on monday?

Controlling the Number of Database Scheduler (DBMS_SCHEDULER) Jobs That Can Execute Concurrently

The maximum number of database scheduler jobs that can run concurrently on each Oracle instance is primarily controlled by the parameter JOB_QUEUE_PROCESSES. The default value is the lesser of 20*CPU_COUNT or SESSIONS/4. I think 20 jobs per CPU is usually far too high because gives the scheduler the potential to swamp the CPU. Therefore, I usually reduce this parameter, often setting it to the same value as CPU_COUNT, so if you have 10 vCPUs per instance, you can run 10 concurrent jobs on each instance.  

However, this is a database-wide parameter. 

• What if you want to restrict different jobs to a different number of concurrently executing instances? 
• Or, you may have a more complex rule where different jobs have different weights? 

You can create a named resource with DBMS_SCHEDULER.CREATE_RESOURCE and give it a certain number of units. Then you can specify the number of units of which resource a particular job consumes with DBMS_SCHEDULER.SET_RESOURCE_CONSTRAINT. This must be done before the job is enabled, and then the job can be enabled afterward. 

Test 1: Separate Resources For Each Job

In this test: 

• Each TEST_An job runs for 30 seconds and consumes 2 units of resource A, which has 10 units, so five jobs can run. 
• Each TEST_Bn job runs for 30 seconds and consumes 1 unit of resource B, which has 3 units, so three jobs continue. 
• The constraints on the two types of jobs are independent.

BEGIN
    DBMS_SCHEDULER.create_resource (
      resource_name    => 'TEST_RESOURCE_A',
      units            => 10,
      status           => 'ENFORCE_CONSTRAINTS',
      constraint_level => 'JOB_LEVEL');
    DBMS_SCHEDULER.create_resource (
      resource_name    => 'TEST_RESOURCE_B',
      units            => 3,
      status           => 'ENFORCE_CONSTRAINTS',
      constraint_level => 'JOB_LEVEL');
END;
/
BEGIN
   FOR i IN 1..10 LOOP
     dbms_scheduler.create_job (
       job_name=> 'TEST_A'||i,
       job_type=> 'PLSQL_BLOCK',
       job_action=> 'BEGIN DBMS_LOCK.SLEEP(30); END;',
       start_date=> sysdate,
       enabled=> false);
     dbms_scheduler.create_job (
       job_name=> 'TEST_B'||i,
       job_type=> 'PLSQL_BLOCK',
       job_action=> 'BEGIN DBMS_LOCK.SLEEP(30); END;',
       start_date=> sysdate,
       enabled=> false);
     DBMS_SCHEDULER.set_resource_constraint (
      object_name   => 'TEST_A'||i,
      resource_name => 'TEST_RESOURCE_A',
      units         => 2);     
     DBMS_SCHEDULER.set_resource_constraint (
      object_name   => 'TEST_B'||i,
      resource_name => 'TEST_RESOURCE_B',
      units         => 1);     
    dbms_scheduler.enable('TEST_A'||i);
    dbms_scheduler.enable('TEST_B'||i);
   END LOOP;
 END;
 /

You can see when each job started and finished in ALL_SCHEDULER_JOB_RUN_DETAILS.

set pages 99
column job_name format a8
column status format a10
clear screen
select log_id, log_date, job_name, status, actual_start_date, run_duration
from all_scheduler_job_run_details where job_name like 'TEST%' 
and actual_start_date >= TRUNC(SYSDATE)+…/24
order by actual_start_date

• The first five TEST_A jobs and the first 3 TEST_B jobs ran. As the groups all finished after exactly 30s, new groups were run.  I've added spacing to illustrate the groups of jobs that run together.

    LOG_ID LOG_DATE                             JOB_NAME STATUS     ACTUAL_START_DATE                           RUN_DURATION       
---------- ------------------------------------ -------- ---------- ------------------------------------------- -------------------
   7747242 21/12/2023 06:42:42.106062000 -05:00 TEST_A1  SUCCEEDED  21/12/2023 11:42:11.578114000 EUROPE/LONDON +00 00:00:31.000000
   7747244 21/12/2023 06:42:42.105168000 -05:00 TEST_A2  SUCCEEDED  21/12/2023 11:42:11.924307000 EUROPE/LONDON +00 00:00:30.000000
   7747246 21/12/2023 06:42:42.615880000 -05:00 TEST_A3  SUCCEEDED  21/12/2023 11:42:12.171116000 EUROPE/LONDON +00 00:00:30.000000
   7747248 21/12/2023 06:42:42.615938000 -05:00 TEST_A4  SUCCEEDED  21/12/2023 11:42:12.208987000 EUROPE/LONDON +00 00:00:30.000000
   7747250 21/12/2023 06:42:42.615895000 -05:00 TEST_A5  SUCCEEDED  21/12/2023 11:42:12.247785000 EUROPE/LONDON +00 00:00:30.000000

   7747210 21/12/2023 06:42:43.680210000 -05:00 TEST_B1  SUCCEEDED  21/12/2023 11:42:13.323724000 EUROPE/LONDON +00 00:00:30.000000
   7747212 21/12/2023 06:42:43.681465000 -05:00 TEST_B5  SUCCEEDED  21/12/2023 11:42:13.356243000 EUROPE/LONDON +00 00:00:30.000000
   7747214 21/12/2023 06:42:43.680210000 -05:00 TEST_B2  SUCCEEDED  21/12/2023 11:42:13.387883000 EUROPE/LONDON +00 00:00:30.000000

   7747276 21/12/2023 06:43:17.947304000 -05:00 TEST_A6  SUCCEEDED  21/12/2023 11:42:47.543438000 EUROPE/LONDON +00 00:00:30.000000
   7747278 21/12/2023 06:43:17.947331000 -05:00 TEST_B3  SUCCEEDED  21/12/2023 11:42:47.543510000 EUROPE/LONDON +00 00:00:30.000000
   7747280 21/12/2023 06:43:17.949158000 -05:00 TEST_B4  SUCCEEDED  21/12/2023 11:42:47.758469000 EUROPE/LONDON +00 00:00:30.000000
   7747282 21/12/2023 06:43:17.947824000 -05:00 TEST_A7  SUCCEEDED  21/12/2023 11:42:47.759084000 EUROPE/LONDON +00 00:00:30.000000
   7747284 21/12/2023 06:43:18.457503000 -05:00 TEST_A8  SUCCEEDED  21/12/2023 11:42:47.966750000 EUROPE/LONDON +00 00:00:30.000000
   7747286 21/12/2023 06:43:18.457438000 -05:00 TEST_B6  SUCCEEDED  21/12/2023 11:42:48.063658000 EUROPE/LONDON +00 00:00:30.000000
   7747320 21/12/2023 06:43:19.008041000 -05:00 TEST_A10 SUCCEEDED  21/12/2023 11:42:48.846141000 EUROPE/LONDON +00 00:00:30.000000
   7747322 21/12/2023 06:43:19.008081000 -05:00 TEST_A9  SUCCEEDED  21/12/2023 11:42:48.846239000 EUROPE/LONDON +00 00:00:30.000000

   7747332 21/12/2023 06:43:49.215439000 -05:00 TEST_B9  SUCCEEDED  21/12/2023 11:43:19.165493000 EUROPE/LONDON +00 00:00:30.000000
   7747334 21/12/2023 06:43:49.729057000 -05:00 TEST_B7  SUCCEEDED  21/12/2023 11:43:19.262625000 EUROPE/LONDON +00 00:00:30.000000
   7747336 21/12/2023 06:43:49.726501000 -05:00 TEST_B8  SUCCEEDED  21/12/2023 11:43:19.262675000 EUROPE/LONDON +00 00:00:30.000000

   7747290 21/12/2023 06:44:23.992734000 -05:00 TEST_B10 SUCCEEDED  21/12/2023 11:43:53.567006000 EUROPE/LONDON +00 00:00:30.000000

Test 2: One Resource Used by Two Jobs

In this second test, both jobs use RESOURCE_A which still has 10 units.

BEGIN
  FOR i IN 1..10 LOOP
    dbms_scheduler.create_job (
      job_name=> 'TEST_A'||i,
      job_type=> 'PLSQL_BLOCK',
      job_action=> 'BEGIN DBMS_LOCK.SLEEP(30); END;',
      start_date=> sysdate,
      enabled=> false);
    dbms_scheduler.create_job (
      job_name=> 'TEST_B'||i,
      job_type=> 'PLSQL_BLOCK',
      job_action=> 'BEGIN DBMS_LOCK.SLEEP(30); END;',
      start_date=> sysdate,
      enabled=> false);
    DBMS_SCHEDULER.set_resource_constraint (
      object_name   => 'TEST_A'||i,
      resource_name => 'TEST_RESOURCE_A',
      units         => 2);     
    DBMS_SCHEDULER.set_resource_constraint (
      object_name   => 'TEST_B'||i,
      resource_name => 'TEST_RESOURCE_A', 
      units         => 1);     
    dbms_scheduler.enable('TEST_A'||i);
    dbms_scheduler.enable('TEST_B'||i);
  END LOOP;
END;
/

Now, we can run 5 TEST_A jobs and 10 test B jobs, or a combination. 

• So initially we had 2 TEST_A jobs (that consume 4 units) and 6 TEST_B jobs (that consume 6 units). This completely consumed RESOURCE_A which only has 10 units.  No new jobs that use this resource could start until others were completed.
• Next, we got 4 TEST_A jobs (that consume 8 units) and 2 TEST_B jobs (that consume 2 units), so again this consumed the whole of RESOURCE_A and again no further jobs could run that require this resource until others were completed.

    LOG_ID LOG_DATE                             JOB_NAME STATUS     ACTUAL_START_DATE                           RUN_DURATION       
---------- ------------------------------------ -------- ---------- ------------------------------------------- -------------------
   7747540 21/12/2023 13:40:10.170737000 -05:00 TEST_B1  SUCCEEDED  21/12/2023 18:39:39.718608000 EUROPE/LONDON +00 00:00:30.000000
   7747542 21/12/2023 13:40:10.169080000 -05:00 TEST_B2  SUCCEEDED  21/12/2023 18:39:39.971882000 EUROPE/LONDON +00 00:00:30.000000
   7747544 21/12/2023 13:40:10.169451000 -05:00 TEST_B3  SUCCEEDED  21/12/2023 18:39:40.012029000 EUROPE/LONDON +00 00:00:30.000000
   7747546 21/12/2023 13:40:10.680827000 -05:00 TEST_B4  SUCCEEDED  21/12/2023 18:39:40.258398000 EUROPE/LONDON +00 00:00:30.000000
   7747548 21/12/2023 13:40:10.680943000 -05:00 TEST_B5  SUCCEEDED  21/12/2023 18:39:40.300213000 EUROPE/LONDON +00 00:00:30.000000
   7747590 21/12/2023 13:40:10.680683000 -05:00 TEST_B6  SUCCEEDED  21/12/2023 18:39:40.343663000 EUROPE/LONDON +00 00:00:30.000000
   7747574 21/12/2023 13:40:11.231396000 -05:00 TEST_A1  SUCCEEDED  21/12/2023 18:39:40.730872000 EUROPE/LONDON +00 00:00:30.000000
   7747576 21/12/2023 13:40:11.231575000 -05:00 TEST_A5  SUCCEEDED  21/12/2023 18:39:40.786089000 EUROPE/LONDON +00 00:00:30.000000

   7747594 21/12/2023 13:40:40.376871000 -05:00 TEST_A2  SUCCEEDED  21/12/2023 18:40:10.271696000 EUROPE/LONDON +00 00:00:30.000000
   7747598 21/12/2023 13:40:40.888493000 -05:00 TEST_A6  SUCCEEDED  21/12/2023 18:40:10.679917000 EUROPE/LONDON +00 00:00:30.000000
   7747600 21/12/2023 13:40:40.889568000 -05:00 TEST_A7  SUCCEEDED  21/12/2023 18:40:10.680655000 EUROPE/LONDON +00 00:00:30.000000
   7747614 21/12/2023 13:40:41.401080000 -05:00 TEST_B10 SUCCEEDED  21/12/2023 18:40:11.304750000 EUROPE/LONDON +00 00:00:30.000000
   7747656 21/12/2023 13:40:42.975067000 -05:00 TEST_A3  SUCCEEDED  21/12/2023 18:40:12.598174000 EUROPE/LONDON +00 00:00:30.000000

   7747672 21/12/2023 13:40:51.168059000 -05:00 TEST_B9  SUCCEEDED  21/12/2023 18:40:21.061653000 EUROPE/LONDON +00 00:00:30.000000

   7747678 21/12/2023 13:41:13.183397000 -05:00 TEST_A4  SUCCEEDED  21/12/2023 18:40:42.789196000 EUROPE/LONDON +00 00:00:30.000000
   7747680 21/12/2023 13:41:13.182999000 -05:00 TEST_A8  SUCCEEDED  21/12/2023 18:40:43.093332000 EUROPE/LONDON +00 00:00:30.000000
   7747682 21/12/2023 13:41:13.183455000 -05:00 TEST_A9  SUCCEEDED  21/12/2023 18:40:43.093346000 EUROPE/LONDON +00 00:00:30.000000
   7747616 21/12/2023 13:41:16.729148000 -05:00 TEST_B7  SUCCEEDED  21/12/2023 18:40:46.287305000 EUROPE/LONDON +00 00:00:30.000000
   7747618 21/12/2023 13:41:16.729207000 -05:00 TEST_B8  SUCCEEDED  21/12/2023 18:40:46.287313000 EUROPE/LONDON +00 00:00:30.000000

   7747684 21/12/2023 13:41:23.423360000 -05:00 TEST_A10 SUCCEEDED  21/12/2023 18:40:53.317328000 EUROPE/LONDON +00 00:00:30.000000

You can also do this if you have a chain of sub-jobs. You would have a program that would be called for each step in the chain, and the resource constraint is applied to the program instead of the job. I will demonstrate this in another blog post.

My thanks to Tim Hall for his blog post Scheduler (DBMS_SCHEDULER) Enhancements in Oracle Database 12c Release 2 (12.2).