- 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 Clas
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.706Verifying 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 799DBA_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_idUSER_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 SUCCEEDEDALL_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
@go-faster.co.uk on BlueSky
Posts
No comments :
Post a Comment