- Job Classes and the Database Scheduler
- Demonstration of Scheduler Jobs (DBMS_SCHEDULER) with Job Class
- 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 errorsThis 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 26ALL_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
@go-faster.co.uk on BlueSky
Posts
No comments :
Post a Comment