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.

Deadlock within DML statements

Oracle maintain a very detailed note Troubleshooting "ORA-00060 Deadlock Detected" Errors (Doc ID 62365.1).  

• This document includes an example that generates a deadlock.  It uses procedural code involving data modification language (DML) statements that each only update a single row.  
• There is also a link to another document that explains how you can produce a deadlock in an autonomous transaction.  ORA-00060 Single-Resource Deadlock Occurs on Autonomous Transaction (Doc ID 1511700.1).

This blog demonstrates that it is also possible to produce a deadlock with a single DML statement that updates multiple rows in a different order than another, potentially identical, statement.  

What is a Deadlock?

Oracle’s note explains that “A deadlock occurs when a session (A) wants a resource held by another session (B), but that session also wants a resource held by the first session (A). There can be more than 2 sessions involved, but the idea is the same.”

The key point is that two or more sessions demand the same resources in a different order.

It is not a Database Error

“NOTE: Deadlock errors are usually not the underlying root cause of a problem, rather they are more likely to be an indicator of another issue in the application or elsewhere. Once the resultant trace file has been examined … to determine the objects involved, it is then worth thinking about what could be causing such a deadlock - for example a batch program being run more than once by mistake or in the wrong order, or by not following programming standards in an application.

Identification and Resolution of the underlying issue then makes the error redundant.”

Diagnostic information produced by a Deadlock

"ORA-00060 error normally writes the error message in the alert.log, together with the name of the trace file created. The exact format of this varies between Oracle releases. The trace file will be written to the directory indicated by the USER_DUMP_DEST or BACKGROUND_DUMP_DEST, depending on the type of process that creates the trace file.

The trace file will contain a deadlock graph and additional information similar to that shown below. This is the trace output from the above example, which signalled an ORA-00060…"

 The trace file always contains this reminder:

“DEADLOCK DETECTED ( ORA-00060 ) [Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following information may aid in determining the deadlock"

This is followed by a deadlock graph that shows the sessions and database locks involved, and hence the object being locked.   The SQL statements involved are also in the trace.

Deadlock graph:

                      ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00050018-000004fa        22     132     X             19     191           X
TX-00070008-00000461        19     191     X             22     132           X

session 132: DID 0001-0016-00000005     session 191: DID 0001-0013-0000000C
session 191: DID 0001-0013-0000000C     session 132: DID 0001-0016-00000005

Set Processing SQL Demonstration

This is a simplified example of producing a deadlock using the same SQL statement, executing in different sessions with different indexes, and therefore updating the rows in a different order.

Setup

I have created a PL/SQL function that calls DBMS_SESSION.SLEEP to pause the session for a specified number of seconds before returning the current system timestamp.  The purpose of this is simply to slow down the update so that it is easier to demonstrate the deadlock without creating a much larger table.

CREATE OR REPLACE FUNCTION my_slow(p_seconds NUMBER) RETURN timestamp IS 
BEGIN
  dbms_session.sleep(p_seconds);
  RETURN systimestamp; 
END;
/

I create a small table with just 10 rows and three columns.  

• Column A has a sequence of integer values, 1 to 10.  This column is the primary key.
• Column B has random numerical values in the range 1 to 100, but when the rows are sorted by this column, they will come out in a different order.  This column is the subject of another index.
• Column C is a timestamp that will be updated with the system timestamp during the test so that we can see what order the rows are updated in. 

CREATE TABLE t 
(a number, b number, c timestamp
,CONSTRAINT t_pk PRIMARY KEY (a))
/
CREATE INDEX t_b ON t (b)
/
INSERT INTO t (a, b)
SELECT level, dbms_random.value(1,100) FROM DUAL 
CONNECT BY level <= 10
/
COMMIT;
EXEC dbms_stats.gather_table_stats(user,'T');

Statement 1

I will update the table T in each of the two sessions with an almost identical update statement.  The only difference between the statements is the column referenced in the where clause, and it is that which dictates the index used.

UPDATE t
SET c = my_slow(1)
WHERE a > 0
/

If I run each of these statements in isolation, they are successful.  I can obtain the execution plan and see when the rows were updated.  The statement updates at the rate of 1 row per second, because the MY_SLOW() function includes a 1-second pause.

In the first statement, the statement uses the primary key index on column A, and we can see that the rows are updated in the order of values in column A because Oracle has range-scanned the primary key index T_PK.

A full scan would have produced the same order in this test, but I want to emphasise that the problem occurs with a change of index.

Plan hash value: 2174628095

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |      |    10 |    40 |     2   (0)| 00:00:01 |
|   1 |  UPDATE           | T    |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| T_PK |    10 |    40 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

SELECT * FROM t ORDER BY c;

         A          B C                  
---------- ---------- -------------------
         1 43.6037759 25/06/2025 17:08:03
         2 75.8443964 25/06/2025 17:08:04
         3 32.7061872 25/06/2025 17:08:05
         4 92.3717375 25/06/2025 17:08:07
         5 99.2611075 25/06/2025 17:08:08
         6 18.9198972 25/06/2025 17:08:09
         7 21.8558534 25/06/2025 17:08:10
         8 15.9224485 25/06/2025 17:08:11
         9 94.3695186 25/06/2025 17:08:12
        10 38.7300478 25/06/2025 17:08:13

Statement 2

In the second statement, the where clause has a condition on column B.

UPDATE t
SET c = my_slow(1)
WHERE b > 0
/

Now the update statement range scans index T_B on column B, and we can see that the rows were updated in the order of the values in column B.

Plan hash value: 2569189006

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | UPDATE STATEMENT  |      |    10 |   230 |     2   (0)| 00:00:01 |
|   1 |  UPDATE           | T    |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| T_B  |    10 |   230 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

SELECT * FROM t ORDER BY c;
         A          B C                  
---------- ---------- -------------------
         8 15.9224485 25/06/2025 17:08:15
         6 18.9198972 25/06/2025 17:08:16
         7 21.8558534 25/06/2025 17:08:17
         3 32.7061872 25/06/2025 17:08:18
        10 38.7300478 25/06/2025 17:08:19
         1 43.6037759 25/06/2025 17:08:20
         2 75.8443964 25/06/2025 17:08:21
         4 92.3717375 25/06/2025 17:08:22
         9 94.3695186 25/06/2025 17:08:23
         5 99.2611075 25/06/2025 17:08:24

Deadlock

If I run the two update statements simultaneously in different sessions, then one of them succeeds, and the other fails with a deadlock error.  

UPDATE t
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

After numerous tests, it appears to be arbitrary which statement succeeds and which fails.  It is just a matter of which statement gets slightly ahead and which detects the deadlock first.

QED

Thus, it is possible to produce a deadlock solely through SQL set-based processing, without any procedural code.  Two similar DML statements, differing only in the index they use, and therefore the order in which they process rows, produced a deadlock.  

Like other deadlocks, it is the difference in the order of processing that is the root cause.