Demonstration of Parallel Execution tasks (DBMS_PARALLEL_EXECUTE) with Job Class

This post is the last part in a series that discusses the use of job classes with the Oracle database scheduler.

1. Job Classes and the Database Scheduler
2. Demonstration of Scheduler Jobs (DBMS_SCHEDULER) with Job Class
3. Demonstration of Parallel Execution tasks (DBMS_PARALLEL_EXECUTE) with Job Clas

A job class can be assigned to all the tasks created when using DBMS_PARALLEL_EXECUTE to run a piece of SQL in concurrent scheduler jobs.  The privileges are the same as required for DBMS_SCHEDULER jobs.

Demonstration Slow Function

This time, I need a slow function that I can call in my SQL

CREATE OR REPLACE FUNCTION GFC_BATCH_USER.my_slow_fn(p_n IN INTEGER) RETURN INTEGER IS 
  l_num_rows INTEGER;
BEGIN
  with t as (select /*+MATERIALIZE*/ level n FROM dual CONNECT BY LEVEL <= p_n)
  select count(*) INTO l_num_rows from t
  connect by n > prior n
  start with n = 1;
  dbms_output.put_line('my_slow_fn('||p_n||'):'||l_num_rows||' rows returned');
  RETURN l_num_rows;
END;
/

select GFC_BATCH_USER.my_slow_fn(20) from dual
/

  GFC_BATCH_USER.MY_SLOW_FN(20)
-------------------------------
                         524288

my_slow_fn(20):524288 rows returned

Setup Table for Parallel SQL

I am going to create a table with 30 rows, each row will create a job that is executed in parallel up to the maximum number of concurrent jobs.  

alter session set current_schema=GFC_BATCH_USER  ;
create table t (a number, b number, c date, d number, constraint t_pk primary key(a));
truncate table t; 
insert into t (a) select level from dual connect by level <= 30;
commit;
select * from t;

         A          B C                                     D
---------- ---------- ---------------------------- ----------
         1
         2
         3
...
        29                                                   
        30

Executing the Task

During this test, JOB_QUEUE_PROCESSES was set to 6, and is running on a RAC system with 2 nodes. I will get 6 jobs on each of the 2 instances, 12 in all, and the 30 requests will be distributed across them.

Note that the job class is specified when the task is run with DBMS_PARALLEL_EXECUTE.RUN_TASK.

exec DBMS_PARALLEL_EXECUTE.STOP_TASK('myslowtask');
exec DBMS_PARALLEL_EXECUTE.DROP_TASK('myslowtask');

DECLARE
  l_sql_stmt1 VARCHAR2(1000) := 'SELECT a, a FROM GFC_BATCH_USER.t';
  l_sql_stmt2 VARCHAR2(1000) := 'UPDATE GFC_BATCH_USER.t SET b = GFC_BATCH_USER.my_slow_fn(24 + mod(a,5)), c=SYSDATE, d=SYS_CONTEXT(''USERENV'',''SID'') WHERE a BETWEEN :start_id AND :end_id';
  l_try NUMBER := 0;
  l_status NUMBER;
  l_task_name VARCHAR2(30) := 'myslowtask';
BEGIN
  -- Create the TASK
  DBMS_PARALLEL_EXECUTE.CREATE_TASK(l_task_name);

  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL(task_name=>l_task_name, table_owner=>'GFC_BATCH_USER', table_name=>'T', table_column=>'A', chunk_size=>1);

  -- Execute the DML in parallel
  DBMS_PARALLEL_EXECUTE.RUN_TASK(l_task_name, l_sql_stmt2, DBMS_SQL.NATIVE, parallel_level=>30, job_class=>'MY_JOB_CLASS');
END;
/

The session that submits the jobs waits for all of them to complete.

PL/SQL procedure successfully completed.

Elapsed: 00:01:25.706

Verifying the Task

After the parallel execution has completed, we can see that each row in the table has been updated.

• B is the number of rows returned from the function
• C is the timestamp of the update, which occurs when the slow function completes.
• D is the job's session ID.  

select * from GFC_BATCH_USER.t
/

         A          B C                          D
---------- ---------- ----------------- ----------
         1   16777216 27/11/25 22:04:40        656
         2   33554432 27/11/25 22:04:40        340
         3   67108864 27/11/25 22:04:40        184
         4  134217728 27/11/25 22:04:40        811
         5    8388608 27/11/25 22:04:41        798
         6   16777216 27/11/25 22:04:41        502
         7   33554432 27/11/25 22:04:45        798
         8   67108864 27/11/25 22:04:46        799
         9  134217728 27/11/25 22:04:46        351
        10    8388608 27/11/25 22:04:46        189
        11   16777216 27/11/25 22:04:46         39
        12   33554432 27/11/25 22:04:46        196
        13   67108864 27/11/25 22:04:46        325
        14  134217728 27/11/25 22:04:49        656
        15    8388608 27/11/25 22:04:50        502
        16   16777216 27/11/25 22:04:51        189
        17   33554432 27/11/25 22:04:55        502
        18   67108864 27/11/25 22:04:55         39
        19  134217728 27/11/25 22:04:59        340
        20    8388608 27/11/25 22:05:00        189
        21   16777216 27/11/25 22:05:03        798
        22   33554432 27/11/25 22:05:05        196
        23   67108864 27/11/25 22:05:05        189
        24  134217728 27/11/25 22:05:13        798
        25    8388608 27/11/25 22:05:14        502
        26   16777216 27/11/25 22:05:18        502
        27   33554432 27/11/25 22:05:19        184
        28   67108864 27/11/25 22:05:24        196
        29  134217728 27/11/25 22:05:25        325
        30    8388608 27/11/25 22:05:25        799

DBA_PARALLEL_EXECUTE_TASKS

The task is completed, but has not been dropped.  So, I can see the job prefix, the job class and the SQL statement.

SELECT task_owner, task_name, chunk_type, status, table_owner, table_name, number_column
, job_prefix, job_class, sql_stmt FROM dba_parallel_execute_tasks;

TASK_OWNER           TASK_NAME  CHUNK_TYPE   STATUS     TABLE_OWNER          TABLE_NAME Column     JOB_PREFIX   JOB_CLASS   
-------------------- ---------- ------------ ---------- -------------------- ---------- ---------- ------------ ------------
SQL_STMT                                                                                                                
------------------------------------------------------------------------------------------------------------------------
GFC_BATCH_USER       myslowtask NUMBER_RANGE FINISHED   GFC_BATCH_USER       T          A          TASK$_184314 MY_JOB_CLASS
UPDATE GFC_BATCH_USER.t SET b = GFC_BATCH_USER.my_slow_fn(24+mod(a,5)), c=SYSDATE, d=SYS_CONTEXT('USERENV','SID') WH
ERE a BETWEEN :start_id AND :end_id

USER_PARALLEL_EXECUTE_CHUNKS

SELECT chunk_id, task_name, status, start_id, end_id, job_name
FROM user_parallel_execute_chunks;

  CHUNK_ID TASK_NAME  STATUS       START_ID     END_ID JOB_NAME            
---------- ---------- ---------- ---------- ---------- --------------------
    620725 myslowtask PROCESSED           1          1 TASK$_184314_1      
    620726 myslowtask PROCESSED           2          2 TASK$_184314_3      
    620727 myslowtask PROCESSED           3          3 TASK$_184314_7      
    620728 myslowtask PROCESSED           4          4 TASK$_184314_5      
    620729 myslowtask PROCESSED           5          5 TASK$_184314_11     
    620730 myslowtask PROCESSED           6          6 TASK$_184314_9      
    620731 myslowtask PROCESSED           7          7 TASK$_184314_11     
    620732 myslowtask PROCESSED           8          8 TASK$_184314_10     
    620733 myslowtask PROCESSED           9          9 TASK$_184314_2      
    620734 myslowtask PROCESSED          10         10 TASK$_184314_12     
    620735 myslowtask PROCESSED          11         11 TASK$_184314_6      
    620736 myslowtask PROCESSED          12         12 TASK$_184314_4      
    620737 myslowtask PROCESSED          13         13 TASK$_184314_8      
    620738 myslowtask PROCESSED          14         14 TASK$_184314_1      
    620739 myslowtask PROCESSED          15         15 TASK$_184314_9      
    620740 myslowtask PROCESSED          16         16 TASK$_184314_12     
    620741 myslowtask PROCESSED          17         17 TASK$_184314_9
    620742 myslowtask PROCESSED          18         18 TASK$_184314_6
    620743 myslowtask PROCESSED          19         19 TASK$_184314_3
    620744 myslowtask PROCESSED          20         20 TASK$_184314_12
    620745 myslowtask PROCESSED          21         21 TASK$_184314_11
    620746 myslowtask PROCESSED          22         22 TASK$_184314_4
    620747 myslowtask PROCESSED          23         23 TASK$_184314_12
    620748 myslowtask PROCESSED          24         24 TASK$_184314_11
    620749 myslowtask PROCESSED          25         25 TASK$_184314_9
    620750 myslowtask PROCESSED          26         26 TASK$_184314_9
    620751 myslowtask PROCESSED          27         27 TASK$_184314_7
    620752 myslowtask PROCESSED          28         28 TASK$_184314_4
    620753 myslowtask PROCESSED          29         29 TASK$_184314_8
    620754 myslowtask PROCESSED          30         30 TASK$_184314_10

Once the jobs are finished, ALL_SCHEDULER_RUNNING_JOBS returns no rows

select * from all_Scheduler_running_jobs
/

ALL_SCHEDULER_JOB_LOG

The scheduler job log reports that the job ran and records the job class.

select log_id, log_date, owner, job_name, job_class, operation, status
from   all_scheduler_job_log
where  job_name IN (SELECT job_name from user_parallel_execute_chunks)
order by log_date desc
/

    LOG_ID LOG_DATE                        OWNER                JOB_NAME             JOB_CLASS    OPERATION  STATUS    
---------- ------------------------------- -------------------- -------------------- ------------ ---------- ----------
   1432798 27/11/25 22.06.03.722515000 GMT GFC_BATCH_USER       TASK$_184314_8       MY_JOB_CLASS RUN        SUCCEEDED 
   1432840 27/11/25 22.05.59.990596000 GMT GFC_BATCH_USER       TASK$_184314_11      MY_JOB_CLASS RUN        SUCCEEDED 
   1432838 27/11/25 22.05.56.539066000 GMT GFC_BATCH_USER       TASK$_184314_3       MY_JOB_CLASS RUN        SUCCEEDED 
   1432832 27/11/25 22.05.52.895848000 GMT GFC_BATCH_USER       TASK$_184314_1       MY_JOB_CLASS RUN        SUCCEEDED 
   1432790 27/11/25 22.05.49.598773000 GMT GFC_BATCH_USER       TASK$_184314_2       MY_JOB_CLASS RUN        SUCCEEDED 
   1432788 27/11/25 22.05.47.204949000 GMT GFC_BATCH_USER       TASK$_184314_4       MY_JOB_CLASS RUN        SUCCEEDED 
   1432836 27/11/25 22.05.42.118069000 GMT GFC_BATCH_USER       TASK$_184314_5       MY_JOB_CLASS RUN        SUCCEEDED 
   1432796 27/11/25 22.05.39.495460000 GMT GFC_BATCH_USER       TASK$_184314_12      MY_JOB_CLASS RUN        SUCCEEDED 
   1432834 27/11/25 22.05.37.321227000 GMT GFC_BATCH_USER       TASK$_184314_7       MY_JOB_CLASS RUN        SUCCEEDED 
   1432792 27/11/25 22.05.34.397795000 GMT GFC_BATCH_USER       TASK$_184314_6       MY_JOB_CLASS RUN        SUCCEEDED 
   1432794 27/11/25 22.05.30.552796000 GMT GFC_BATCH_USER       TASK$_184314_10      MY_JOB_CLASS RUN        SUCCEEDED 
   1432842 27/11/25 22.05.28.175639000 GMT GFC_BATCH_USER       TASK$_184314_9       MY_JOB_CLASS RUN        SUCCEEDED

ALL_SCHEDULER_JOB_RUN_DETAILS

select log_id, log_date, owner, job_name, status, req_start_date, actual_start_date, run_duration, instance_id, session_id, slave_pid, cpu_used, output
from   all_scheduler_job_run_details
where  job_name IN (SELECT job_name from user_parallel_execute_chunks)
order by job_name desc
/

The 12 jobs show the 30 calls to my_slow_fn() and how long they took.  Job requests, such as TASK$_184314_9, that happened to pick up the shorter job requests had time to run 5 jobs,  while TASK$_184314_2 processed only a single longer-running job.

    LOG_ID LOG_DATE                        OWNER                JOB_NAME             STATUS     REQ_START_DATE                           
---------- ------------------------------- -------------------- -------------------- ---------- -----------------------------------------
                                                              Inst Session                                                      
ACTUAL_START_DATE                         RUN_DURATION          ID ID         SLAVE_PID                      CPU_USED           
----------------------------------------- ------------------- ---- ---------- ------------------------------ -------------------
OUTPUT                                                                                                                                                
--------------------------------------------------------------------------------------------------------------------------------------------
   1432842 27/11/25 22.05.28.184200000 GMT GFC_BATCH_USER       TASK$_184314_9       SUCCEEDED  27/11/25 22.04.40.949320000 EUROPE/LONDON 
27/11/25 22.04.41.103830000 EUROPE/LONDON +00 00:00:47.000000    1 502,40742  67745                          +00 00:00:43.390000 
my_slow_fn(25):16777216 rows returned                                                                                                                    
my_slow_fn(24):8388608 rows returned                                                                                                                     
my_slow_fn(26):33554432 rows returned                                                                                                                    
my_slow_fn(24):8388608 rows returned                                                                                                                     
my_slow_fn(25):16777216 rows returned                                                                                                                    

   1432798 27/11/25 22.06.03.723089000 GMT GFC_BATCH_USER       TASK$_184314_8       SUCCEEDED  27/11/25 22.04.40.926354000 EUROPE/LONDON 
27/11/25 22.04.46.599530000 EUROPE/LONDON +00 00:01:17.000000    2 325,64741  72626                          +00 00:01:12.800000 
my_slow_fn(27):67108864 rows returned                                                                                                                    
my_slow_fn(28):134217728 rows returned                                                                                                                   

   1432834 27/11/25 22.05.37.321783000 GMT GFC_BATCH_USER       TASK$_184314_7       SUCCEEDED  27/11/25 22.04.40.897917000 EUROPE/LONDON 
27/11/25 22.04.40.902120000 EUROPE/LONDON +00 00:00:56.000000    1 184,51217  67747                          +00 00:00:52.880000 
my_slow_fn(27):67108864 rows returned                                                                                                                    
my_slow_fn(26):33554432 rows returned                                                                                                                    

   1432792 27/11/25 22.05.34.398412000 GMT GFC_BATCH_USER       TASK$_184314_6       SUCCEEDED  27/11/25 22.04.40.879716000 EUROPE/LONDON 
27/11/25 22.04.46.515314000 EUROPE/LONDON +00 00:00:48.000000    2 39,12988   71851                          +00 00:00:43.950000 
my_slow_fn(25):16777216 rows returned                                                                                                                    
my_slow_fn(27):67108864 rows returned                                                                                                                    

   1432836 27/11/25 22.05.42.118559000 GMT GFC_BATCH_USER       TASK$_184314_5       SUCCEEDED  27/11/25 22.04.40.862536000 EUROPE/LONDON 
27/11/25 22.04.40.902486000 EUROPE/LONDON +00 00:01:01.000000    1 811,31076  67743                          +00 00:00:57.780000 
my_slow_fn(28):134217728 rows returned                                                                                                                   

   1432788 27/11/25 22.05.47.205444000 GMT GFC_BATCH_USER       TASK$_184314_4       SUCCEEDED  27/11/25 22.04.40.846000000 EUROPE/LONDON 
27/11/25 22.04.46.515138000 EUROPE/LONDON +00 00:01:01.000000    2 196,61889  71847                          +00 00:00:56.680000 
my_slow_fn(26):33554432 rows returned                                                                                                                    
my_slow_fn(26):33554432 rows returned                                                                                                                    
my_slow_fn(27):67108864 rows returned                                                                                                                    

   1432838 27/11/25 22.05.56.539267000 GMT GFC_BATCH_USER       TASK$_184314_3       SUCCEEDED  27/11/25 22.04.40.827636000 EUROPE/LONDON 
27/11/25 22.04.40.902634000 EUROPE/LONDON +00 00:01:16.000000    1 340,61644  67741                          +00 00:01:11.690000 
my_slow_fn(26):33554432 rows returned                                                                                                                    
my_slow_fn(28):134217728 rows returned                                                                                                                   

   1432790 27/11/25 22.05.49.599028000 GMT GFC_BATCH_USER       TASK$_184314_2       SUCCEEDED  27/11/25 22.04.40.808503000 EUROPE/LONDON 
27/11/25 22.04.46.515308000 EUROPE/LONDON +00 00:01:03.000000    2 351,50039  71845                          +00 00:00:59.390000 
my_slow_fn(28):134217728 rows returned                                                                                                                   

   1432796 27/11/25 22.05.39.496026000 GMT GFC_BATCH_USER       TASK$_184314_12      SUCCEEDED  27/11/25 22.04.41.028696000 EUROPE/LONDON 
27/11/25 22.04.46.555765000 EUROPE/LONDON +00 00:00:53.000000    2 189,41910  72624                          +00 00:00:48.770000 
my_slow_fn(24):8388608 rows returned                                                                                                                     
my_slow_fn(25):16777216 rows returned                                                                                                                    
my_slow_fn(24):8388608 rows returned                                                                                                                     
my_slow_fn(27):67108864 rows returned                                                                                                                    

   1432840 27/11/25 22.05.59.990799000 GMT GFC_BATCH_USER       TASK$_184314_11      SUCCEEDED  27/11/25 22.04.40.998815000 EUROPE/LONDON 
27/11/25 22.04.41.103787000 EUROPE/LONDON +00 00:01:19.000000    1 798,31456  67749                          +00 00:01:15.300000 
my_slow_fn(24):8388608 rows returned                                                                                                                     
my_slow_fn(26):33554432 rows returned                                                                                                                    
my_slow_fn(25):16777216 rows returned                                                                                                                    
my_slow_fn(28):134217728 rows returned                                                                                                                   

   1432794 27/11/25 22.05.30.553345000 GMT GFC_BATCH_USER       TASK$_184314_10      SUCCEEDED  27/11/25 22.04.40.972242000 EUROPE/LONDON 
27/11/25 22.04.46.515430000 EUROPE/LONDON +00 00:00:44.000000    2 799,25589  71849                          +00 00:00:39.410000 
my_slow_fn(27):67108864 rows returned                                                                                                                    
my_slow_fn(24):8388608 rows returned                                                                                                                     

   1432832 27/11/25 22.05.52.896353000 GMT GFC_BATCH_USER       TASK$_184314_1       SUCCEEDED  27/11/25 22.04.40.788698000 EUROPE/LONDON 
27/11/25 22.04.40.793091000 EUROPE/LONDON +00 00:01:12.000000    1 656,39533  67739                          +00 00:01:07.410000 
my_slow_fn(25):16777216 rows returned                                                                                                                    
my_slow_fn(28):134217728 rows returned                                                                                                                   

Verify Consumer Group in Active Session History

ASH can be used to verify that the consumer group really was set for each of these jobs.

with h as (
select user_id, session_id, session_serial#, top_level_sql_id, sql_id, consumer_group_id, module, action
,      min(sample_Time) min_Sample_time, max(Sample_Time) max_sample_time, sum(usecs_per_row)/1e6 ash_Secs
from   gv$active_Session_History h
where  module = 'DBMS_SCHEDULER'
and    action IN (SELECT job_name from user_parallel_execute_chunks)
group by user_id, session_id, session_serial#, top_level_sql_id, sql_id, consumer_group_id, module, action
)
select h.user_id, u.username, h.session_id, h.session_serial#, h.consumer_group_id, cg.consumer_group
,      h.module, h.action, h.ash_Secs, h.min_Sample_time, h.max_sample_time
from   h
  left outer join dba_users u on u.user_id = h.user_id 
  left outer join dba_rsrc_consumer_groups cg on cg.consumer_group_id = h.consumer_Group_id
order by max_sample_time desc
/

It confirms that the consumer group was switched to HIGH_BATCH_GROUP

                               Session Session Consumer                                                                    ASH                                            
   USER_ID USERNAME                  ID Serial# Group ID CONSUMER_GROUP       MODULE               ACTION                  Secs MIN_SAMPLE_TIME       MAX_SAMPLE_TIME      
---------- -------------------- ------- ------- -------- -------------------- -------------------- -------------------- ------- --------------------- ---------------------
       815 GFC_BATCH_USER           325   64741   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_8         77.18 27/11/25 22.04.46.781 27/11/25 22.06.02.963
       815 GFC_BATCH_USER           798   31456   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_11        79.02 27/11/25 22.04.41.593 27/11/25 22.05.59.614
       815 GFC_BATCH_USER           340   61644   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_3         75.02 27/11/25 22.04.41.593 27/11/25 22.05.55.613
       815 GFC_BATCH_USER           656   39533   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_1         72.02 27/11/25 22.04.41.593 27/11/25 22.05.52.612
       815 GFC_BATCH_USER           351   50039   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_2         63.18 27/11/25 22.04.46.781 27/11/25 22.05.48.960
       815 GFC_BATCH_USER           196   61889   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_4         61.18 27/11/25 22.04.46.781 27/11/25 22.05.46.959
       815 GFC_BATCH_USER           811   31076   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_5         61.02 27/11/25 22.04.41.593 27/11/25 22.05.41.609
       815 GFC_BATCH_USER           189   41910   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_12        53.18 27/11/25 22.04.46.781 27/11/25 22.05.38.957
       815 GFC_BATCH_USER           184   51217   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_7         56.01 27/11/25 22.04.41.593 27/11/25 22.05.36.608
       815 GFC_BATCH_USER            39   12988   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_6         48.18 27/11/25 22.04.46.781 27/11/25 22.05.33.956
       815 GFC_BATCH_USER           799   25589   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_10        44.17 27/11/25 22.04.46.781 27/11/25 22.05.29.955
       815 GFC_BATCH_USER           502   40742   738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       TASK$_184314_9         47.01 27/11/25 22.04.41.593 27/11/25 22.05.27.605

Demonstration of Scheduler Jobs (DBMS_SCHEDULER) with Job Class

This post is the second part in a series that discusses the use of job classes with the Oracle database scheduler.

1. Job Classes and the Database Scheduler
2. Demonstration of Scheduler Jobs (DBMS_SCHEDULER) with Job Class
3. Demonstration of Parallel Execution tasks (DBMS_PARALLEL_EXECUTE) with Job Class

This demonstration uses DBMS_SCHEDULER to create a job allocated to a specific resource manager consumer group via a job class.

Job Classes Cannot be Controlled With DBMS_JOB

The DBMS_JOB package was deprecated in Oracle 12c.  One of its limitations is that it cannot specify a job class.  Another reason to use DBMS_SCHEDULER instead, which superceded it.  However, one limited reason to continue using DBMS_JOB is that it avoids the implicit commit in DBMS_SCHEDULER.CREATE_JOB.  Thus, it is possible to create a job (perhaps in a trigger), but defer execution until the current transaction commits.

Demonstration Slow Procedure

The below function runs for several seconds, consuming CPU.  This demonstration then creates jobs that execute this function so that they run for a controllable period of time. 

CREATE OR REPLACE PROCEDURE GFC_BATCH_USER.my_slow(p_n IN INTEGER) IS 
  l_num_rows INTEGER;
BEGIN
  with t as (select /*+MATERIALIZE*/ level n FROM dual CONNECT BY LEVEL <= p_n)
  select count(*) INTO l_num_rows from t
  connect by n > prior n
  start with n = 1;
  dbms_output.put_line('my_slow('||p_n||'):'||l_num_rows||' rows returned');
END;
/
show errors

This procedure runs for about 1 second with a parameter of 23.  Its duration roughly doubles as the parameter is increased by 1.  With a parameter of 29, it runs for about 1 minute.

set timi on serveroutput on
exec GFC_BATCH_USER.my_slow(23);

Create a Job with a Job Class 

The job_class attribute can be specified when a job is created with DBMS_SCHEDULER.CREATE_JOB or altered afterwards with DBMS_SCHEDULER.SET_ATTRIBUTE.

• See DBMS_SCHEDULER Job Attribute Values 
• In this demonstration, the job calls the stored procedure directly, rather than via an anonymous PL/SQL block, passing a parameter with DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE.  See also Tim Hall: Oracle ANYDATA Type. 

exec dbms_scheduler.drop_job('GFC_BATCH_USER.MY_SLOW_JOB');

BEGIN
  dbms_Scheduler.create_job
  (job_name => 'GFC_BATCH_USER.MY_SLOW_JOB'
  ,job_type => 'STORED_PROCEDURE'
  ,job_action => 'GFC_BATCH_USER.MY_SLOW'
  ,number_of_arguments => 1
  ,job_class => 'SYS.MY_JOB_CLASS'
  ,enabled => FALSE
  ,auto_drop => FALSE
  );
  
  /*alternately attribute can be set separately
  dbms_scheduler.set_attribute 
  (name => 'GFC_BATCH_USER.MY_SLOW_JOB'
  ,attribute=>'JOB_CLASS'
  ,value=>'SYS.MY_JOB_CLASS');*/

  --see https://oracle-base.com/articles/misc/anydata
  dbms_scheduler.set_job_anydata_value 
  (job_name => 'GFC_BATCH_USER.MY_SLOW_JOB'
  ,argument_position => 1
  ,argument_value => SYS.ANYDATA.convertNumber(29) --30=>2 mins
  );
END;
/

Verify the job has been created and that the argument is set correctly.  At the moment, it hasn't run because it is not enabled.

select owner, job_name, job_type, job_action, number_of_arguments, job_class, enabled, auto_drop
from   all_scheduler_jobs
where  job_name = 'MY_SLOW_JOB'
/
select owner, job_name, argument_position, argument_Type
,      SYS.ANYDATA.accessNumber(anydata_value) anydata_value
from   all_scheduler_job_args
where  job_name = 'MY_SLOW_JOB'
/ 

OWNER            JOB_NAME             JOB_TYPE         JOB_ACTION                     Args JOB_CLASS    ENABLED  Drop 
---------------- -------------------- ---------------- ------------------------------ ---- ------------ -------- -----
GFC_BATCH_USER   MY_SLOW_JOB          STORED_PROCEDURE GFC_BATCH_USER.MY_SLOW          1 MY_JOB_CLASS FALSE    TRUE 

                                       Arg                                   
OWNER            JOB_NAME              Pos ARGUMENT_TYPE        ANYDATA_VALUE
---------------- -------------------- ---- -------------------- -------------
GFC_BATCH_USER   MY_SLOW_JOB             1 UNDEFINED                       29

Executing the Job

Finally, enable the job to execute it.  The job is set not to auto-drop on completion, so it can be run many times by repeating this step.

exec dbms_scheduler.enable(name => 'GFC_BATCH_USER.MY_SLOW_JOB');

Verifying the Job is Running

Verify that the job is running and that both the running job record and the session both show the correct consumer group.

select j.owner, j.job_name, j.running_instance, j.session_id, j.resource_Consumer_group, s.module, s.action, s.resource_consumer_group, s.seconds_in_wait
from   all_scheduler_running_jobs j, sys.gv_$session s
where  j.session_id = s.sid and j.running_instance = s.inst_id
/

The resource consumer group on v$session confirms in real-time that it has been set by the job class.

OWNER            JOB_NAME             RUNNING_INSTANCE SESSION_ID RESOURCE_CONSUMER_GROUP
---------------- -------------------- ---------------- ---------- ------------------------
MODULE                           ACTION                           RESOURCE_CONSUMER_GROUP  SECONDS_IN_WAIT
-------------------------------- -------------------------------- ------------------------ ---------------
GFC_BATCH_USER   MY_SLOW_JOB                         2         32 HIGH_BATCH_GROUP         
DBMS_SCHEDULER                   MY_SLOW_JOB                      HIGH_BATCH_GROUP                      26

ALL_SCHEDULER_JOB_LOG

Job Logging shows job class.  

select log_id, log_date, owner, job_name, job_class, status
from   all_scheduler_job_log where job_name = 'MY_SLOW_JOB' 
order by log_date desc
/

LOG_ID LOG_DATE                        OWNER            JOB_NAME             JOB_CLASS            STATUS    
---------- ------------------------------- ---------------- -------------------- -------------------- ----------
   1426216 27/11/25 15.11.43.364349000 GMT GFC_BATCH_USER   MY_SLOW_JOB          MY_JOB_CLASS         SUCCEEDED 
   1424946 27/11/25 08.59.20.795062000 GMT GFC_BATCH_USER   MY_SLOW_JOB          MY_JOB_CLASS         SUCCEEDED 
   1424956 27/11/25 08.57.11.622288000 GMT GFC_BATCH_USER   MY_SLOW_JOB          MY_JOB_CLASS         SUCCEEDED

ALL_SCHEDULER_JOB_RUN_DETAILS

The job run details report start date/time, log date/time, duration, CPU and job output.

select log_id, log_date, owner, job_name, status, instance_id, session_id, actual_start_date, run_duration, cpu_used, output
from   all_scheduler_job_run_details where job_name = 'MY_SLOW_JOB' 
order by log_date desc
/

    LOG_ID LOG_DATE                        OWNER            JOB_NAME             STATUS       ID SESSION_ID  
---------- ------------------------------- ---------------- -------------------- ---------- ---- ------------
ACTUAL_START_DATE                         RUN_DURATION        CPU_USED           
----------------------------------------- ------------------- -------------------
OUTPUT                                                                                                                  
--------------------------------------------------------------------------------------------------------------
   1431896 27/11/25 17.57.23.535680000 GMT GFC_BATCH_USER   MY_SLOW_JOB          SUCCEEDED     2 32,44433     
27/11/25 17.56.19.423438000 EUROPE/LONDON +00 00:01:04.000000 +00 00:01:03.580000 
my_slow(29):268435456 rows returned                                                                                     

   1431862 27/11/25 17.55.20.448915000 GMT GFC_BATCH_USER   MY_SLOW_JOB          SUCCEEDED     1 492,20046    
27/11/25 17.54.16.443151000 EUROPE/LONDON +00 00:01:04.000000 +00 00:01:03.610000 
my_slow(29):268435456 rows returned

Verify Consumer Group in Active Session History

Finally, Active Session History data confirms that the consumer group was switched to HIGH_BATCH_GROUP.   

with h as (
select user_id, session_id, session_serial#, top_level_sql_id, sql_id, consumer_group_id, module, action
,      min(sample_Time) min_Sample_time, max(Sample_Time) max_sample_time
from   gv$active_Session_History h
where  module = 'DBMS_SCHEDULER'
and    action = 'MY_SLOW_JOB'
group by user_id, session_id, session_serial#, top_level_sql_id, sql_id, consumer_group_id, module, action
)
select h.user_id, u.username, h.session_id, h.session_serial#
,      h.consumer_group_id, cg.consumer_group, h.module, h.action, h.min_Sample_time, h.max_sample_time
from   h
  left outer join dba_users u on u.user_id = h.user_id 
  left outer join dba_rsrc_consumer_groups cg on cg.consumer_group_id = h.consumer_Group_id
order by max_sample_time desc
/

                                Session Session                                                                                                                             
   USER_ID USERNAME                  ID Serial# CONSUMER_GROUP_ID CONSUMER_GROUP       MODULE               ACTION               MIN_SAMPLE_TIME       MAX_SAMPLE_TIME      
---------- -------------------- ------- ------- ----------------- -------------------- -------------------- -------------------- --------------------- ---------------------
         0 SYS                       32   44433            738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       MY_SLOW_JOB          27/11/25 17.56.20.102 27/11/25 17.57.23.116
         0 SYS                      492   20046            738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       MY_SLOW_JOB          27/11/25 17.54.16.916 27/11/25 17.55.19.932
         0 SYS                      638   46963            738019 HIGH_BATCH_GROUP     DBMS_SCHEDULER       MY_SLOW_JOB          27/11/25 17.47.21.809 27/11/25 17.48.24.888

Job Classes and the Database Scheduler

This post is the first part in a series that discusses the use of job classes with the Oracle database scheduler.

1. Job Classes and the Database Scheduler
2. Demonstration of Scheduler Jobs (DBMS_SCHEDULER) with Job Class
3. Demonstration of Parallel Execution tasks (DBMS_PARALLEL_EXECUTE) with Job Class

Introduction

If you are using the database scheduler to run batch jobs, I recommend specifying a job class for each job, even if that requires creating a job class for every job. If you have developed a custom resource manager plan, sessions will usually be mapped to consumer groups by mapping rules on session attributes (database service, database user, client machine, client program, client OS user, module or action). You can also explicitly map a job within a job class to specific consumer groups and database services.  

For example, an application may execute the same code in different places, possibly as a part of online processing and also within scheduled batch processing, and then require different prioritisation for each by mapping to different consumer groups.  Application instrumentation may map online execution to one consumer group, but execution via a scheduler job can be explicitly mapped to a different consumer group via a job class.

Some code change is required to specify a job class when creating scheduler jobs.  Job class attributes can be altered at any time without requiring further code changes.

If you have not yet developed a custom resource manager plan, then at least use the default resource plan.  Performance metrics will indicate when you have run out of CPU and may need a custom resource manager plan to prioritise processing.  

What is a Job Class?

A job class is an Oracle Scheduler object that enables the scheduler administrator to group jobs for logical purposes; to assign the same set of attribute values to member jobs, to set service affinity for member jobs, to set resource allocation for member jobs, or to group jobs for prioritisation.

See also

• Scheduler Objects
• Scheduler Concepts: Job Classes

Various attributes can be set on a job class:

• Comment
• Log History Retention: Number of days for which job logging is retained.
• Logging Level: Off, Failed Runs, Runs, Full.
• Resource Consumer Group: This explicitly sets the consumer group for jobs in the job class.  Explicitly setting the consumer group always overrides any other mapping.
• Database service: The database service to which the jobs in the job class have affinity.   Some services may be available only on certain RAC instances; any job in this class will only run on those instances.
• If both the resource_consumer_group and service attributes are set for a job class, and if the service is mapped to a resource consumer group, the resource_consumer_group attribute takes precedence.

• NB: Resource Manager is an Enterprise Edition feature.  Job classes also control job logging and RAC affinity.  Job classes are available on Standard Edition, but their consumer group setting has no effect. 

Creating/Altering a Job Class

Job Classes can be dropped and created thus:

exec dbms_scheduler.drop_job_class(job_class_name => 'MY_JOB_CLASS', force=>TRUE);

begin
  dbms_scheduler.create_job_class
  (job_class_name => 'MY_JOB_CLASS'
  ,resource_consumer_group => 'HIGH_BATCH_GROUP'
  ,comments => 'dmk: A sample job class for demonstration purposes'
  );
END;
/

Attributes on existing job classes can be set and reset thus:

BEGIN
  dbms_scheduler.set_attribute
  (name=>'MY_JOB_CLASS'
  ,attribute=>'resource_consumer_group'
  ,value=>'HIGH_BATCH_GROUP');

  dbms_scheduler.set_attribute
  (name=>'MY_JOB_CLASS'
  ,attribute=>'log_history'
  ,value=>1 
  );
END;
/

• See also Job Class Attributes (DBMS_SCHEDULER: Job Class Attribute Values)

NB: For this demonstration, I have set the log retention to 1 day.  It is not a general recommendation.

Verify that the job class is created correctly by querying ALL_SCHEDULER_JOB_CLASSES.  Note that job classes can only be owned by SYS, never by other users.

SELECT *
FROM   all_scheduler_job_classes
WHERE  job_class_name like 'MY%CLASS'
ORDER BY 1,2
/
                                                                                    Logging        Log                                                                                 
OWNER            JOB_CLASS_NAME                 RESOURCE_CONSUMER_GROUP  SERVICE    Level      History COMMENTS                                                                        
---------------- ------------------------------ ------------------------ ---------- ---------- ------- -----------------------------------------------------------------
SYS              MY_JOB_CLASS                   HIGH_BATCH_GROUP                    RUNS             1 dmk: A sample job class for demonstration purposes

Privileges

Job Class Execute Privileges

The user who submits the job request that specifies a job class (not the job's owner) must be granted execute permission on the job class.  In my tests, the job is owned by GFC_BATCH_USER, but it is executed by both GFC_BATCH_USER and GFC_GENERIC_READONLY;

GRANT EXECUTE ON sys.my_job_class TO GFC_BATCH_USER  ;
GRANT EXECUTE ON sys.my_job_class TO GFC_GENERIC_READONLY;

• Note that "Job classes defined with DBMS_SCHEDULER.CREATE_JOB_CLASS are not owned by the schema user but by user SYS (as inferred by looking at the error message itself)." - See Oracle Support Note 2087507.1: ORA-27476 After Creating Job Class, using SET_ATTRIBUTE to assign New Job Class To Existing Job

Verify execute privilege thus:

SELECT * FROM dba_tab_privs where table_name = 'MY_JOB_CLASS';

GRANTEE              OWNER            TABLE_NAME           GRANTOR              PRIVILEGE  GRA HIE COM TYPE                     INH
-------------------- ---------------- -------------------- -------------------- ---------- --- --- --- ------------------------ ---
GFC_GENERIC_READONLY SYS              MY_JOB_CLASS         SYS                  EXECUTE    NO  NO  NO  JOB CLASS                NO 
GFC_BATCH_USER       SYS              MY_JOB_CLASS         SYS                  EXECUTE    NO  NO  NO  JOB CLASS                NO

Switch Consumer Group Privilege

The job class explicitly switches the consumer group.  The user who submits the job request (not the user who owns the job) needs the privilege to switch consumer groups.

BEGIN
  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
  (grantee_name   => 'GFC_GENERIC_READONLY'
  ,consumer_group => 'HIGH_BATCH_GROUP'
  ,grant_option   => FALSE);

  DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
  (grantee_name   => 'GFC_BATCH_USER'
  ,consumer_group => 'HIGH_BATCH_GROUP'
  ,grant_option   => FALSE);
END;
/

In the following posts, I demonstrate how to set job classes on scheduler jobs and parallel tasks.

Changing the Database Scheduler Maintenance Windows

The purpose of object statistics is to inform the cost-based optimizer about the size and shape of your data, enabling it to make informed decisions about how to execute SQL statements.  You need to tell it the truth about your data.  If you don’t, you can’t be surprised if it makes a poor decision.  Thus, statistics need to be kept up to date as data changes, including not just changes to the volume of data, but also the ranges and distribution of data values in columns.

In fact, most of the time, we refresh statistics so that the execution plan remains the same!

Table Monitoring

Oracle tracks the number of rows inserted, updated or deleted for each table since statistics were last refreshed.  The tracking is done in memory by the database and is periodically flushed out to the dictionary.  This feature was known as table monitoring when it was first introduced in Oracle 8i, but since 11g, it has been just a part of the database and is enabled for all tables.  

Stale Statistics

If the sum of the number of rows inserted, updated and deleted as a percentage of the number of rows in the table (as recorded in the statistics) is greater than or equal to the stale threshold (by default 10%), then the statistics on that table are considered to be stale. The STALE_PERCENT threshold can be set and changed as a statistics preference either globally or for specific tables or groups of tables.

• See DBMS_STATS: SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

If you need statistics to be collected more frequently on a particular table, then the stale threshold can be reduced as required (it can be set to any non-negative value; it does not have to be an integer).

Maintenance Windows

Oracle delivers several maintenance jobs that run in delivered maintenance windows on the database scheduler.  There are maintenance windows for each day of the week, MONDAY_WINDOW, etc.  That Automatic Optimizer Statistics Collection job gathers statistics on tables, indexes and their partitions whose statistics are either missing or stale.  

• Index statistics are gathered if the CASCADE preference is set.  
• Collection of partition statistics is controlled by the GRANULARITY and other preferences.
• Tables or partitions whose statistics are locked (set LOCK_TABLE_STATS and LOCK_PARTITION_STATS) are omitted.

The delivered weekday windows open at 10pm for 4 hours, and the weekend windows are open for 24 hours.  10pm on weekday evenings may or may not be a good time to collect statistics on a particular system.  If it isn’t, the window should be changed to a more convenient time.

I work on several systems where the default maintenance window coincides with the overnight batch workload.  

• On one system, the daytime workload is much less than the overnight batch, so the weekday maintenance windows have simply been moved to 10am, during the working day.
• On another system, it has been decided to run statistics twice per weekday as the system transitions between overnight batch processing and daytime online processing.  Thus, each workload begins with fresh statistics.  The weekday windows now open at 5am and 8pm, each for 3 hours.  The weekend windows still open for 24 hours, but also at 5am.
• Some customers shut down non-production cloud environments overnight to save costs.  In this case, the maintenance window should be moved to a time when the environment is usually running; otherwise, statistics may never be refreshed.

In the second case, the delivered windows were changed by the following script.  It uses DBMS_SCHEDULER.SET_ATTRIBUTE to set the REPEAT_INTERVAL attribute of the delivered window.

Scheduler Time Zone

"The way the scheduler handles time zones can be a little confusing at first. If the scheduler default_timezone is not specified, it attempts to determine it from the operating system. If that isn't possible, it is set to NULL.

…

When you create [or alter] a job, the time zone for the job is determined using the time zone associated with the start_date parameter. If that is not supplied, the default_timezone is used. In the case of the start_date value, using SYSTIMESTAMP will fix an offset, rather than honouring the daylight savings rules. A more consistent result is produced … where the time zone is specified explicitly"

• See Oracle-Base: Scheduler (DBMS_SCHEDULER) in Oracle Database 10g Onward, Time Zones

You may need to explicitly specify the time zone when you create or alter scheduler jobs.

In this case, the system is running in the Mexico City time zone (UTC-6), but the scheduler time zone is UTC. 

----------------------------------------------------------------------------------------------------
--current time zone settings
----------------------------------------------------------------------------------------------------
--ALTER SESSION SET TIME_ZONE = '-6:00';
SELECT systimestamp, DBTIMEZONE, DBMS_SCHEDULER.STIME, SESSIONTIMEZONE FROM DUAL;
--select * from v$TIMEZONE_names where tzname like '%Mex%';
--select systimestamp at time zone 'America/Mexico_City' from dual;
----------------------------------------------------------------------------------------------------
--get current scheduler timezone
----------------------------------------------------------------------------------------------------
declare
  l_attrib VARCHAR2(100);
begin
  dbms_Scheduler.get_scheduler_attribute('default_timezone',l_attrib);
  dbms_output.put_line(l_attrib);
end;
/

In this case, the system is running in the Mexico City time zone (UTC-6), but the scheduler time zone is UTC. 

SYSTIMESTAMP                       DBTIME STIME
---------------------------------- ------ -----------------------------------------
SESSIONTIMEZONE                                                            
---------------------------------------------------------------------------
09/01/26 11.20.21.946810000 -06:00 +00:00 09/01/26 17.20.21.947216000 EUROPE/LONDON 
Europe/London                                                              
…
Etc/UTC
…

Altering the Maintenance Windows

The following script alters the DEFAULT_MAINTENANCE_PLAN windows for each day of the week.  

• A time zone is specified in a constant at the top of the PL/SQL block.  It is used to specify the start time attribute for each window as the current system time.  That also determines the time zone of the repeat interval.
• I have specified the time zone by name, rather than a UTC offset, because I want the jobs to follow the daylight saving adjustment for that region.
• The duration and repeat interval of each window are also set.

• See also Administrator’s Guide – Configuring Maintenance Windows 

This method of multiple openings for a single window is effective when each opening lasts for the same duration. If I want the weekday morning and evening openings to be for different durations (for example, 2 and 3 hours, respectively), or at different minutes past the hour (for example, 5.15am and 7.30pm), then I would need to define additional windows and add them to the MAINTENANCE_WINDOW_GROUP window group.

• The script is available on GitHub.

REM gfc_default_maintenance_plan_window.sql
REM alter weekday schedule for maintenance window to run for 3 hours twice per day at 5am and 8pm, and for 24 hours on weekends at 5am
…
DECLARE
  --k_time_zone CONSTANT VARCHAR2(30) := ''; --do not specify timezone
  --k_time_zone CONSTANT VARCHAR2(30) := 'GMT'; --specify timezone
  --k_time_zone CONSTANT VARCHAR2(30) := 'Europe/London'; --specify timezone
  k_time_zone CONSTANT VARCHAR2(30) := 'America/Mexico_City'; --specify timezone

  l_duration INTEGER;  l_by VARCHAR2(30);
BEGIN
  FOR i IN ( --update all windows in DEFAULT_MAINTENANCE_PLAN
    select * 
    from   dba_Scheduler_Windows
    where  resource_plan = 'DEFAULT_MAINTENANCE_PLAN'
    and    regexp_like(window_name,'(MON|TUES|WEDNES|THURS|FRI|SATUR|SUN)DAY_WINDOW')
  ) LOOP

    IF regexp_like(i.window_name,'(MON|TUES|WEDNES|THURS|FRI)DAY_WINDOW') THEN 
      l_duration := 3; --duration of weekday opening in hours
      l_by := ';byhour=5,20'; --two openings of weekday windows
    ELSE
      l_duration := 24; --duration of weekend opening in hours
      l_by := ';byhour=5'; --one opening of weekend windows
    END IF;
    dbms_output.put_line('Window:'||i.owner||'.'||i.window_name||': schedule:'||l_by||' :'||l_duration||' hours');
    
    dbms_scheduler.disable --disable window, otherwise change does not apply until it next opens 
    (name      => i.owner||'.'||i.window_name
    );

    IF k_time_zone IS NOT NULL THEN --set start date with time zone, it applies to repeat interval
      dbms_scheduler.set_attribute
      (name      => i.owner||'.'||i.window_name
      ,attribute => 'START_DATE'
      ,value     => systimestamp at time zone k_time_zone
      );
    END IF;

    dbms_scheduler.set_attribute --set duration for each window opening
    (name      => i.owner||'.'||i.window_name
    ,attribute => 'DURATION'
    ,value     => numtodsinterval(l_duration, 'hour')
    );

    dbms_scheduler.set_attribute --set times that window opens
    (name      => i.owner||'.'||i.window_name
    ,attribute => 'REPEAT_INTERVAL'
    ,value     => 'freq=daily;byday='||SUBSTR(i.window_name,1,3)||l_by||';byminute=0;bysecond=0'
    );

    dbms_scheduler.enable
    (name      => i.owner||'.'||i.window_name
    );

    for j in (
      select * from dba_scheduler_window_groups
      where window_group_name IN('MAINTENANCE_WINDOW_GROUP','ORA$AT_WGRP_OS','ORA$AT_WGRP_SA','ORA$AT_WGRP_SQ')
    ) LOOP
      DBMS_SCHEDULER.add_window_group_member --add window to window group
      (group_name  => j.window_group_name
      ,window_list => i.owner||'.'||i.window_name);
    END LOOP;

  END LOOP;
END;
/

The script reports the windows that it updates.

Window:SYS.MONDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.TUESDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.WEDNESDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.THURSDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.FRIDAY_WINDOW: schedule:;byhour=5,20 :3 hours
Window:SYS.SATURDAY_WINDOW: schedule:;byhour=5 :24 hours
Window:SYS.SUNDAY_WINDOW: schedule:;byhour=5 :24 hours

PL/SQL procedure successfully completed.

After running the configuration script, we can check that the settings have been configured.

select * from dba_scheduler_window_groups
where window_group_name = 'MAINTENANCE_WINDOW_GROUP'
/
select * from dba_Scheduler_wingroup_members
where window_group_name = 'MAINTENANCE_WINDOW_GROUP'
/
select * from dba_Scheduler_Windows
where resource_plan = 'DEFAULT_MAINTENANCE_PLAN'
order by next_Start_date
/

There are 7 delivered windows in the MAINTENANCE_WINDOW_GROUP.  We can see their last run date, repeat interval, and next scheduled run date.

                                Number                                                                                                              
WINDOW_GROUP_NAME        ENABL Windows NEXT_START_DATE                                  COMMENTS                                                    
------------------------ ----- ------- ------------------------------------------------ --------------------------------------------------
MAINTENANCE_WINDOW_GROUP TRUE        7 09-JAN-26 08.00.00.000000 PM AMERICA/MEXICO_CITY Window group for Automated Maintenance                      
…

WINDOW_GROUP_NAME        WINDOW_NAME     
------------------------ ----------------
MAINTENANCE_WINDOW_GROUP MONDAY_WINDOW   
MAINTENANCE_WINDOW_GROUP TUESDAY_WINDOW  
MAINTENANCE_WINDOW_GROUP WEDNESDAY_WINDOW
MAINTENANCE_WINDOW_GROUP THURSDAY_WINDOW 
MAINTENANCE_WINDOW_GROUP FRIDAY_WINDOW   
MAINTENANCE_WINDOW_GROUP SATURDAY_WINDOW 
MAINTENANCE_WINDOW_GROUP SUNDAY_WINDOW   
…
                                                Schedule                    Schedule
OWNER WINDOW_NAME      RESOURCE_PLAN            Owner      SCHEDULE_NAME    Type       START_DATE                                    REPEAT_INTERVAL
----- ---------------- ------------------------ ---------- ---------------- ---------- --------------------------------------------- ------------------------------------------------------------
                                                    Window
END_DATE                        DURATION            Priority NEXT_START_DATE                                  LAST_START_DATE                                  ENABL ACTIV
------------------------------- ------------------- -------- ------------------------------------------------ ------------------------------------------------ ----- -----
MANUAL_OPEN_TIME                MANUAL_DURATION     COMMENTS
------------------------------- ------------------- --------------------------------------------------
SYS   WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN                             CALENDAR   Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=WED;byhour=5,20;byminute=0;bysecond=0
                                +00 03:00:00.000000 LOW      Wed 21/01/26 05:00:00.000 AMERICA/MEXICO_CITY    Wed 21/01/26 05:00:00.119 AMERICA/MEXICO_CITY    TRUE  TRUE
                                                    Wednesday window for maintenance tasks

SYS   THURSDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN                             CALENDAR   Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=THU;byhour=5,20;byminute=0;bysecond=0
                                +00 03:00:00.000000 LOW      Thu 22/01/26 05:00:00.000 AMERICA/MEXICO_CITY    Thu 15/01/26 20:00:00.023 AMERICA/MEXICO_CITY    TRUE  FALSE
                                                    Thursday window for maintenance tasks

SYS   FRIDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN                             CALENDAR   Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=FRI;byhour=5,20;byminute=0;bysecond=0
                                +00 03:00:00.000000 LOW      Fri 23/01/26 05:00:00.000 AMERICA/MEXICO_CITY    Fri 16/01/26 20:00:00.100 AMERICA/MEXICO_CITY    TRUE  FALSE
                                                    Friday window for maintenance tasks

SYS   SATURDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN                             CALENDAR   Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=SAT;byhour=5;byminute=0;bysecond=0
                                +00 23:00:00.000000 LOW      Sat 24/01/26 05:00:00.000 AMERICA/MEXICO_CITY    Sat 17/01/26 05:00:00.036 AMERICA/MEXICO_CITY    TRUE  FALSE
                                                    Saturday window for maintenance tasks

SYS   SUNDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN                             CALENDAR   Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=SUN;byhour=5;byminute=0;bysecond=0
                                +00 23:00:00.000000 LOW      Sun 25/01/26 05:00:00.000 AMERICA/MEXICO_CITY    Sun 18/01/26 05:00:00.042 AMERICA/MEXICO_CITY    TRUE  FALSE
                                                    Sunday window for maintenance tasks

SYS   MONDAY_WINDOW    DEFAULT_MAINTENANCE_PLAN                             CALENDAR   Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=MON;byhour=5,20;byminute=0;bysecond=0
                                +00 03:00:00.000000 LOW      Mon 26/01/26 05:00:00.000 AMERICA/MEXICO_CITY    Mon 19/01/26 20:00:00.067 AMERICA/MEXICO_CITY    TRUE  FALSE
                                                    Monday window for maintenance tasks

SYS   TUESDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN                             CALENDAR   Wed 14/01/26 05:28:16.000 AMERICA/MEXICO_CITY freq=daily;byday=TUE;byhour=5,20;byminute=0;bysecond=0
                                +00 03:00:00.000000 LOW      Tue 27/01/26 05:00:00.000 AMERICA/MEXICO_CITY    Tue 20/01/26 20:00:00.157 AMERICA/MEXICO_CITY    TRUE  FALSE
                                                    Tuesday window for maintenance tasks
…

After the schedule has been allowed to run for a few days, we use the scheduler job logs to verify that the jobs are running as desired.

select log_id, log_date, owner, job_name, status, error#, actual_start_Date, run_duration, instance_id, session_id, cpu_used 
from dba_scheduler_job_run_details
where owner = 'SYS' 
and job_name like 'ORA$AT_OS_OPT_SY%'
order by /*run_duration desc,*/ log_date desc
fetch first 50 rows only
/

Note that: "In the case of a very long maintenance window, all automated maintenance tasks except Automatic SQL Tuning Advisor are restarted every four hours. This feature ensures that maintenance tasks are run regularly, regardless of window size." - see Database Administrator's Guide, 26 Managing Automated Database Maintenance Tasks, 26.2 About Maintenance Windows

                                                                                                                                               Inst
  LOG_ID LOG_DATE                         OWNER JOB_NAME                STATUS    ERROR# ACTUAL_START_DATE                 RUN_DURATION          ID SESSION_ID CPU_USED
-------- -------------------------------- ----- ----------------------- --------- ------ --------------------------------- ------------------- ---- ---------- -------------------
  455724 Wed 21/01/26 05:00:43.047 -06:00 SYS   ORA$AT_OS_OPT_SY_26423  SUCCEEDED      0 Wed 21/01/26 11:00:02.472 ETC/UTC +00 00:00:41.000000    1 1467,25845 +00 00:00:34.900000
  455582 Tue 20/01/26 20:02:00.474 -06:00 SYS   ORA$AT_OS_OPT_SY_26444  SUCCEEDED      0 Wed 21/01/26 02:00:04.307 ETC/UTC +00 00:01:56.000000    2 1751,14822 +00 00:01:24.520000
  455384 Tue 20/01/26 05:02:35.196 -06:00 SYS   ORA$AT_OS_OPT_SY_26430  SUCCEEDED      0 Tue 20/01/26 11:00:02.728 ETC/UTC +00 00:02:32.000000    1 884,22319  +00 00:02:09.700000
  455262 Mon 19/01/26 20:01:58.846 -06:00 SYS   ORA$AT_OS_OPT_SY_26412  SUCCEEDED      0 Tue 20/01/26 02:00:03.780 ETC/UTC +00 00:01:55.000000    1 884,4883   +00 00:01:39.430000
  455036 Mon 19/01/26 05:00:13.187 -06:00 SYS   ORA$AT_OS_OPT_SY_26402  SUCCEEDED      0 Mon 19/01/26 11:00:02.276 ETC/UTC +00 00:00:11.000000    2 333,21412  +00 00:00:09.000000
  454946 Mon 19/01/26 01:04:03.499 -06:00 SYS   ORA$AT_OS_OPT_SY_26399  SUCCEEDED      0 Mon 19/01/26 07:03:09.820 ETC/UTC +00 00:00:54.000000    2 327,11548  +00 00:00:48.290000
  454872 Sun 18/01/26 21:02:44.802 -06:00 SYS   ORA$AT_OS_OPT_SY_26397  SUCCEEDED      0 Mon 19/01/26 03:02:31.836 ETC/UTC +00 00:00:13.000000    2 1459,27367 +00 00:00:10.710000
  454788 Sun 18/01/26 17:02:54.589 -06:00 SYS   ORA$AT_OS_OPT_SY_26394  SUCCEEDED      0 Sun 18/01/26 23:01:56.036 ETC/UTC +00 00:00:59.000000    2 1455,13997 +00 00:00:52.520000
  454750 Sun 18/01/26 13:01:33.537 -06:00 SYS   ORA$AT_OS_OPT_SY_26392  SUCCEEDED      0 Sun 18/01/26 19:01:15.866 ETC/UTC +00 00:00:18.000000    2 1468,50519 +00 00:00:14.760000
  454666 Sun 18/01/26 09:01:01.283 -06:00 SYS   ORA$AT_OS_OPT_SY_26390  SUCCEEDED      0 Sun 18/01/26 15:00:39.891 ETC/UTC +00 00:00:21.000000    2 1468,38883 +00 00:00:19.730000
  454572 Sun 18/01/26 05:00:25.942 -06:00 SYS   ORA$AT_OS_OPT_SY_26387  SUCCEEDED      0 Sun 18/01/26 11:00:07.639 ETC/UTC +00 00:00:18.000000    2 2017,20454 +00 00:00:14.860000
  454482 Sun 18/01/26 01:07:16.123 -06:00 SYS   ORA$AT_OS_OPT_SY_26383  SUCCEEDED      0 Sun 18/01/26 07:06:18.742 ETC/UTC +00 00:00:57.000000    2 1157,32828 +00 00:00:46.240000
  454412 Sat 17/01/26 21:06:25.203 -06:00 SYS   ORA$AT_OS_OPT_SY_26380  SUCCEEDED      0 Sun 18/01/26 03:05:47.683 ETC/UTC +00 00:00:38.000000    2 1175,48150 +00 00:00:31.870000
  454334 Sat 17/01/26 17:05:37.560 -06:00 SYS   ORA$AT_OS_OPT_SY_26378  SUCCEEDED      0 Sat 17/01/26 23:05:23.029 ETC/UTC +00 00:00:15.000000    2 336,36837  +00 00:00:11.480000
  454302 Sat 17/01/26 13:05:20.405 -06:00 SYS   ORA$AT_OS_OPT_SY_26376  SUCCEEDED      0 Sat 17/01/26 19:04:54.828 ETC/UTC +00 00:00:26.000000    2 871,11733  +00 00:00:22.170000
  454166 Sat 17/01/26 09:04:43.136 -06:00 SYS   ORA$AT_OS_OPT_SY_26374  SUCCEEDED      0 Sat 17/01/26 15:04:23.641 ETC/UTC +00 00:00:19.000000    2 871,14655  +00 00:00:15.770000
  454184 Sat 17/01/26 05:01:40.118 -06:00 SYS   ORA$AT_OS_OPT_SY_26371  SUCCEEDED      0 Sat 17/01/26 11:00:03.076 ETC/UTC +00 00:01:37.000000    1 1442,22115 +00 00:01:18.340000
  453992 Fri 16/01/26 20:00:36.757 -06:00 SYS   ORA$AT_OS_OPT_SY_26365  SUCCEEDED      0 Sat 17/01/26 02:00:05.360 ETC/UTC +00 00:00:31.000000    2 2025,17953 +00 00:00:25.270000
  453434 Fri 16/01/26 05:02:26.340 -06:00 SYS   ORA$AT_OS_OPT_SY_26352  SUCCEEDED      0 Fri 16/01/26 11:00:01.652 ETC/UTC +00 00:02:25.000000    1 338,49711  +00 00:02:04.670000
  453352 Thu 15/01/26 20:05:14.702 -06:00 SYS   ORA$AT_OS_OPT_SY_26336  SUCCEEDED      0 Fri 16/01/26 02:00:06.180 ETC/UTC +00 00:05:09.000000    1 906,60694  +00 00:04:12.300000
  453274 Thu 15/01/26 05:02:56.172 -06:00 SYS   ORA$AT_OS_OPT_SY_26331  SUCCEEDED      0 Thu 15/01/26 11:00:02.280 ETC/UTC +00 00:02:54.000000    1 2026,42084 +00 00:02:16.260000
  453212 Wed 14/01/26 20:04:07.215 -06:00 SYS   ORA$AT_OS_OPT_SY_26344  SUCCEEDED      0 Thu 15/01/26 02:00:02.944 ETC/UTC +00 00:04:04.000000    1 26,35644   +00 00:03:09.810000
…

Conclusion

There is nothing special about running the statistics maintenance job at 10pm on weekdays.  It is perfectly satisfactory for many systems.  However, if it doesn't fit with your system workload, then change it to a time or times that do.

Partition Pruning/Elimination on Reference Partitioned Tables

I discussed locally partitioning the unique index on a reference partitioned table in a previous blog.  Having implemented it, I wanted to confirm what happens when I execute a single-table query on the reference partitioned table.  

It is not possible to specify predicates on the partitioning key columns, because they are on the foreign key table.  However, provided that a query specifies predicates on all the foreign key columns, the database can still prune/eliminate partitions, and it does not probe every partition.

Single Table Query

This example uses the same example tables as the previous blog.  The journal header table, PS_JRNL_HEADER, is the parent and therefore foreign key of the journal line table, PS_JRNL_LN.

CREATE TABLE PS_JRNL_HEADER 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL
…
,CONSTRAINT PS_JRNL_HEADER PRIMARY KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ)
) 
PARTITION BY RANGE (fiscal_year) INTERVAL (1)
(PARTITION FISCAL_YEAR_2016 VALUES LESS THAN (2017))
/

CREATE TABLE PS_JRNL_LN 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL 
,JOURNAL_LINE NUMBER(9,0) NOT NULL
,LEDGER VARCHAR2(10 CHAR) NOT NULL
…
,CONSTRAINT PS_JRNL_LN PRIMARY KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ, JOURNAL_LINE, LEDGER)
,CONSTRAINT PS_JRNL_LN_FK FOREIGN KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ) REFERENCES PS_JRNL_HEADER 
)
PARTITION BY REFERENCE(PS_JRNL_LN_FK)
…
/

My single-table query has literal equality predicates on each of the foreign key columns.

select *
from ps_jrnl_ln
where business_unit = '12345'
and journal_id = 'XX12345678'
and journal_date = TO_DATE('25/05/2021','DD/MM/YYYY')
and unpost_seq = 0
/

This is the resulting execution plan

Plan hash value: 2773029334
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |     1 |   277 |     5   (0)| 00:00:01 |       |       |
|   1 |  PARTITION REFERENCE SINGLE                |            |     1 |   277 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_JRNL_LN |     1 |   277 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    SORT CLUSTER BY ROWID BATCHED           |            |     1 |       |     4   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN                       | PS_JRNL_LN |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("BUSINESS_UNIT"='12345' AND "JOURNAL_ID"='XX12345678' AND "JOURNAL_DATE"=TO_DATE(' 2021-05-25 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "UNPOST_SEQ"=0)

• The execution plan only mentions PS_JRNL_LN. There is no mention of visiting PS_JRNL_HEADER, but this doesn’t mean it didn’t happen. 
• At line 1, PARTITION REFERENCE SINGLE indicates that the database accessed a single partition; it did not scan multiple partitions. 
• The partition start/stop values for table and index accesses are all ‘KEY’.  This indicates that the partition was only determined during execution rather than earlier parsing. Usually, when we specify literal values in a SQL statement, we expect to see literal partition start/stop values.  Here, the partition is looked up for the foreign key values provided, so we get key values during execution. 

What is going on here?  Oracle visits the foreign key table PS_JRNL_HEADER, looking up the foreign key, which is also its primary key, and is the subject of a unique index.   It determines the partition in the foreign key table from the ROWID in the index (even though the index cannot be locally partitioned because it does not contain the partitioning key column).  There is a 1:1 relationship of partitions between the reference partitioned table and its foreign key table.  Thus, Oracle also determines which partition to query in the reference partitioned table, and hence the partition start/stop values mentioned in the execution plan are ‘KEY’ because they are determined at execution time.

Trace Test

To confirm this, I traced the query. The buffer cache was flushed before the test, so that I would see the physical I/O for each block accessed. 

ALTER SYSTEM FLUSH BUFFER_CACHE; 
ALTER SESSION SET tracefile_identifier=DMK1_JRNL_LN_LOOKUP; 
exec dbms_monitor.session_trace_enable(waits => true, binds => true);

select *
from ps_jrnl_ln
where business_unit = '12345'
and journal_id = 'XX12345678'
and journal_date = TO_DATE('25/05/2021','DD/MM/YYYY')
and unpost_seq = 0
/

exec dbms_monitor.session_trace_disable;

Specifying TRACEFILE_IDENTIFIER makes it easy to correctly identify the trace file in v$diag_trace_file.

SELECT * FROM v$DIAG_TRACE_FILE
WHERE trace_filename like '%DMK%'
ORDER BY modify_time desc
/

Then it can be queried from v$diag_trace_file_contents and spooled to a local file (see also Obtaining Trace Files without Access to the Database Server).

clear screen
set pages 0 lines 200 echo off
spool DMK_JRNL_LN_LOOKUP.trc
SELECT payload FROM v$diag_trace_file_contents
WHERE trace_filename = 'xxxxarcx2_ora_235305_DMK1_JRNL_LN_LOOKUP.trc'
ORDER BY line_number
/
spool off

Database Objects and IDs

The trace mentions three object IDs.  I have looked them up in the DBA_OBJECTS view for convenience.

SELECT object_id, object_type, object_name, subobject_name 
FROM dba_objects WHERE objecT_id IN(574371,600163, 574522)
/

OBJECT_ID OBJECT_TYPE          OBJECT_NAME        SUBOBJECT_NAME
---------- -------------------- ------------------ ------------------------------------------------
    574371 INDEX                PS_JRNL_HEADER
    574522 TABLE PARTITION      PS_JRNL_LN         FISCAL_YEAR_2021_ACCOUNTING_PERIOD_07
    600163 INDEX PARTITION      PS_JRNL_LN         FISCAL_YEAR_2021_ACCOUNTING_PERIOD_07

Trace File

1. 3 blocks are read from object 574371.  This is the primary key index PS_JRNL_HEADER, on the table of the same name.  Oracle is looking up the partition on the foreign key on JRNL_LN to get the partition in the reference table.  Only the table is partitioned; this index is not, but Oracle can get the partition from the row ID in the index.  Curiously, Oracle still performs this lookup if the index on the reference partitioned table is not partitioned.  Thus, this is an overhead of reference partitioning, not of whether the index is partitioned, but the foreign key is only looked up once for each foreign key, and then the blocks will be in the buffer cache.
2. There is one multi-block and two single-block reads from the index PS_JRNL_LN, but only from one index partition; FISCAL_YEAR_2021_ACCOUNTING_PERIOD_07.
3. Finally, Oracle looks up table rows by ROWID.  They are all in the table partition that has the same name as the index partition, and this required two single block reads.

Trace file /u01/app/oracle/diag/rdbms/xxxxarcx/xxxxarcx2/trace/xxxxarcx2_ora_235305_DMK1_JRNL_LN_LOOKUP.trc

…

*** TRACE CONTINUED FROM FILE 
/u01/app/oracle/diag/rdbms/xxxxarcx/xxxxarcx2/trace/xxxxarcx2_ora_235305_DMK0_JRNL_LN_LOOKUP.trc ***

…

=====================

PARSING IN CURSOR #140550623318264 len=169 dep=0 uid=130 oct=3 lid=130 tim=1987367209651 hv=686856243 ad='61aa57d40' sqlid='db2cyj8ng161m'

select *

from ps_jrnl_ln

where business_unit = '12345'

and journal_id = 'XX12345678'

and journal_date = TO_DATE('25/05/2021','DD/MM/YYYY')

and unpost_seq = 0



END OF STMT

PARSE #140550623318264:c=123,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2773029334,tim=1987367209651

WAIT #140550623318264: nam='gc cr grant 2-way' ela= 237 p1=97 p2=459395 p3=1 obj#=574371 tim=1987367210207

WAIT #140550623318264: nam='cell single block physical read: flash cache' ela= 358 cellhash#=4239709683 diskhash#=0 bytes=8192 obj#=574371 tim=1987367210612[1]

WAIT #140550623318264: nam='gc cr grant 2-way' ela= 110 p1=39 p2=474285 p3=1 obj#=574371 tim=1987367210865

WAIT #140550623318264: nam='cell single block physical read: flash cache' ela= 335 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574371 tim=1987367211225

WAIT #140550623318264: nam='cell single block physical read: flash cache' ela= 330 cellhash#=4156894774 diskhash#=0 bytes=8192 obj#=574371 tim=1987367211636

EXEC #140550623318264:c=1481,e=2000,p=3,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=2773029334,tim=1987367211709

WAIT #140550623318264: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=574371 tim=1987367211783

WAIT #140550623318264: nam='gc cr multi block grant' ela= 234 p1=69 p2=647975 p3=14 obj#=600163 tim=1987367212195

WAIT #140550623318264: nam='cell multiblock physical read' ela= 205 cellhash#=4156894774 diskhash#=0 bytes=32768 obj#=600163 tim=1987367212436[2]

WAIT #140550623318264: nam='cell single block physical read: xrmem cache' ela= 169 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=600163 tim=1987367212706

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 44 cellhash#=4239709683 diskhash#=0 bytes=8192 obj#=600163 tim=1987367212817

WAIT #140550623318264: nam='gc cr grant 2-way' ela= 71 p1=39 p2=481918 p3=1 obj#=600163 tim=1987367212976

WAIT #140550623318264: nam='cell single block physical read: xrmem cache' ela= 137 cellhash#=4239709683 diskhash#=0 bytes=8192 obj#=600163 tim=1987367213151

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 38 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213303[3]

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 29 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213406

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 28 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213502

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 25 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213563

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 26 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213620

WAIT #140550623318264: nam='cell single block physical read: flash cache' ela= 649 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367214312

FETCH #140550623318264:c=1397,e=3255,p=13,cr=11,cu=0,mis=0,r=4,dep=0,og=1,plh=2773029334,tim=1987367215128

STAT #140550623318264 id=1 cnt=4 pid=0 pos=1 obj=0 op='PARTITION REFERENCE SINGLE PARTITION: KEY KEY (cr=14 pr=16 pw=0 str=1 time=5142 us cost=5 size=277 card=1)'

STAT #140550623318264 id=2 cnt=4 pid=1 pos=1 obj=574372 op='TABLE ACCESS BY LOCAL INDEX ROWID BATCHED PS_JRNL_LN PARTITION: KEY KEY (cr=11 pr=13 pw=0 str=1 time=3240 us cost=5 size=277 card=1)'

STAT #140550623318264 id=3 cnt=4 pid=2 pos=1 obj=0 op='SORT CLUSTER BY ROWID BATCHED (cr=4 pr=7 pw=0 str=1 time=1366 us cost=4 size=0 card=1)'

STAT #140550623318264 id=4 cnt=4 pid=3 pos=1 obj=600104 op='INDEX RANGE SCAN PS_JRNL_LN PARTITION: KEY KEY (cr=4 pr=7 pw=0 str=1 time=1331 us cost=4 size=0 card=1)'

WAIT #140550623318264: nam='SQL*Net message from client' ela= 155007 driver id=1413697536 #bytes=1 p3=0 obj#=574522 tim=1987367370548

CLOSE #140550623318264:c=14,e=13,dep=0,type=0,tim=1987367370652

=====================

…

Locally Partitioned Index 

In my scenario, I found that the number of levels in B-tree index in the local partitions was generally only 2 or 3, rather than 4 in the global non-partitioned version of that index (of course, this will vary from case to case). So local partitioning saved one or two I/Os per index probe. This quickly outweighs the lookup of the journal header table because there are many journal lines per journal header.

SELECT index_owner, partition_name, index_name, num_rows, distinct_keys, blevel, leaf_blocks, status
FROM dba_ind_partitions
WHERE index_name = 'PS_JRNL_LN'
ORDER by partition_position
/

INDEX_OWNE PARTITION_NAME                         INDEX_NAME           NUM_ROWS DISTINCT_KEYS     BLEVEL LEAF_BLOCKS STATUS  
---------- -------------------------------------- ------------------ ---------- ------------- ---------- ----------- --------
…
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_01  PS_JRNL_LN           10189190      10189190          2       32008 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_02  PS_JRNL_LN            5368231       5368231          2       16970 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_03  PS_JRNL_LN            6713612       6713612          2       21132 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_04  PS_JRNL_LN            8500469       8500469          2       27128 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_05  PS_JRNL_LN            7901118       7901118          2       24862 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_06  PS_JRNL_LN           29785888      29785888          3       95734 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_07  PS_JRNL_LN           29978325      29978325          3       96377 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_08  PS_JRNL_LN            8470092       8470092          2       26743 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_09  PS_JRNL_LN           30393756      30393756          3       97669 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_10  PS_JRNL_LN           30649060      30649060          3       98537 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_11  PS_JRNL_LN            9340460       9340460          2       29597 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_12  PS_JRNL_LN           55420790      55420790          3      177193 USABLE  
…

TL;DR 

The single-table lookup of the reference partitioned table does benefit from partition pruning/elimination, although there is no predicate on the partitioning key, but only if there are predicates on each of the foreign key columns. The foreign key becomes a proxy for the predicate on the partitioning key. 

Thus, locally partitioning the primary key index on a reference partitioned table can be effective even if you do not additionally join the foreign key table.