The /*+Go-Faster*/ Oracle Blog: February 2026

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

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

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

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

The /+Go-Faster/ Oracle Blog

Thursday, February 12, 2026

Demonstration of Parallel Execution tasks (DBMS_PARALLEL_EXECUTE) with Job Class

Demonstration Slow Function

Setup Table for Parallel SQL

Executing the Task

Verifying the Task

DBA_PARALLEL_EXECUTE_TASKS

USER_PARALLEL_EXECUTE_CHUNKS

ALL_SCHEDULER_JOB_LOG

ALL_SCHEDULER_JOB_RUN_DETAILS

Verify Consumer Group in Active Session History

Tuesday, February 03, 2026

Demonstration of Scheduler Jobs (DBMS_SCHEDULER) with Job Class

Job Classes Cannot be Controlled With DBMS_JOB

Demonstration Slow Procedure

Create a Job with a Job Class

Executing the Job

Verifying the Job is Running

ALL_SCHEDULER_JOB_LOG

ALL_SCHEDULER_JOB_RUN_DETAILS

Verify Consumer Group in Active Session History

Announcements

My Book

Oracle ACE Director

BlueSky / Twitter

Blog Archive

Tags

Other Oracle Blogs

Other PeopleSoft Blogs

OakTable Network

Oracle ACE Director

©David Kurtz

The /*+Go-Faster*/ Oracle Blog

Thursday, February 12, 2026

Demonstration of Parallel Execution tasks (DBMS_PARALLEL_EXECUTE) with Job Class

Demonstration Slow Function

Setup Table for Parallel SQL

Executing the Task

Verifying the Task

DBA_PARALLEL_EXECUTE_TASKS

USER_PARALLEL_EXECUTE_CHUNKS

ALL_SCHEDULER_JOB_LOG

ALL_SCHEDULER_JOB_RUN_DETAILS

Verify Consumer Group in Active Session History

Tuesday, February 03, 2026

Demonstration of Scheduler Jobs (DBMS_SCHEDULER) with Job Class

Job Classes Cannot be Controlled With DBMS_JOB

Demonstration Slow Procedure

Create a Job with a Job Class

Executing the Job

Verifying the Job is Running

ALL_SCHEDULER_JOB_LOG

ALL_SCHEDULER_JOB_RUN_DETAILS

Verify Consumer Group in Active Session History

Announcements

My Book

Oracle ACE Director

BlueSky / Twitter

Blog Archive

Subscribe

Tags

Other Oracle Blogs

Other PeopleSoft Blogs

OakTable Network

Oracle ACE Director

©David Kurtz

The /+Go-Faster/ Oracle Blog