While updating material for a training class about Database Resource Management I briefly wondered if Instance Caging should apply for scheduler jobs as well. The obvious answer is “yes”, but I wanted to find proof. I hope I did in the end, and wanted to share how I got there.
The test environment
My system is based on the shiny new AMD Ryzen 7 1700X Eight-Core Processor, and it shows as 1s8c16t. I really like it! My 12.2.0.1 database named DEMO is patched to August 2017 – 12.2.0.1.170814 to be precise. It’s difficult to test resource management, and specifically Instance Caging, with a dual-core laptop, so this machine should help!
Test setup
Apart from the database workload I am soon going to launch the system is otherwise idle. I set cpu_count to 4 and made sure I had a resource manager plan active:
SQL> select name, value from v$parameter
2 where name in ('cpu_count','resource_manager_plan');
NAME VALUE
------------------------------ ------------------------------------------------
cpu_count 4
resource_manager_plan SCHEDULER[0x4ABF]:DEFAULT_MAINTENANCE_PLAN
For this case it doesn’t matter that my resource manager plan is associated with the maintenance window. All I needed was some resource manager plan. In production systems the situation is most often somewhat different and proper resource management is crucial.
The scheduler needs something to work with, and I opted for a stored procedure that needlessly burns CPU. Like this one:
SQL> create or replace procedure martin.burn_proc( 2 pi_num_iterations number) 3 as 4 i number; 5 begin 6 for j in 1..pi_num_iterations loop 7 i := dbms_random.random; 8 end loop; 9 end; 10 / Procedure created.
Now all I had to do was to create an anonymous block of PL/SQL scheduling a number of jobs. This little piece of code does just that:
SQL> !cat burn_sched.sql
select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') now from dual;
show user
accept num_jobs number prompt 'how many jobs to schedule? '
prompt creating a number of scheduler jobs
declare
i number;
begin
for i in 1..&num_jobs loop
dbms_scheduler.create_job(
job_name => 'martin.burn_proc_job' || i,
job_type => 'PLSQL_BLOCK',
job_action => 'begin burn_proc(1000000000); end;',
start_date => systimestamp,
enabled => true);
end loop;
dbms_lock.sleep(5);
end;
/
prompt checking for running jobs
select count(*) from dba_scheduler_running_jobs where owner = 'MARTIN';
Now it’s time to run the code!
SQL> @burn_sched
NOW
-------------------
05.11.2017 09:38:49
USER is "MARTIN"
how many jobs to schedule? 3
creating a number of scheduler jobs
old 4: for i in 1..&num_jobs loop
new 4: for i in 1.. 3 loop
PL/SQL procedure successfully completed.
checking for running jobs
COUNT(*)
----------
3
With this first test I wanted to see what happens when keeping the number of jobs lower than the value I defined for cpu_count. Using Active Session History (ASH) as the source for performance data is probably the easiest way to analyse what happened.
Careful: using Active Session History (ASH) requires a license!
Provided you are licensed to use ASH, a query such as the following provides valuable insights:
SQL> select count(*), event, session_state, session_type, username
2 from v$active_session_history ash left join dba_users u on (ash.user_id = u.user_id)
3 where module = 'DBMS_SCHEDULER'
4 and sample_time > to_date('05.11.2017 09:38:49','dd.mm.yyyy hh24:mi:ss')
5 group by event, session_state, session_type, username;
COUNT(*) EVENT SESSION SESSION_TY USERNAME
---------- -------------------- ------- ---------- ----------
89 ON CPU FOREGROUND MARTIN
As you can see, none of the samples in ASH show any waits for CPU. Let’s increase the session count to a value that exceeds my cpu_count of 4:
SQL> @burn_sched
NOW
-------------------
05.11.2017 09:51:45
USER is "MARTIN"
how many jobs to schedule? 5
creating a number of scheduler jobs
old 4: for i in 1..&num_jobs loop
new 4: for i in 1.. 5 loop
PL/SQL procedure successfully completed.
checking for running jobs
COUNT(*)
----------
5
After these 5 jobs completed, I checked ASH again to see if there was a difference:
SQL> select count(*), event, session_state, session_type, username
2 from v$active_session_history ash left join dba_users u on (ash.user_id = u.user_id)
3 where module = 'DBMS_SCHEDULER'
4 and sample_time > to_date('05.11.2017 09:51:45','dd.mm.yyyy hh24:mi:ss')
5 group by event, session_state, session_type, username;
COUNT(*) EVENT SESSION SESSION_TY USERNAME
---------- ------------------------------ ------- ---------- ----------
153 ON CPU FOREGROUND MARTIN
66 resmgr:cpu quantum WAITING FOREGROUND MARTIN
And indeed, there is a number of Resource Manager CPU waits! To me this is proof enough that scheduler jobs also fall into the category of workload that can be caged.
Martins Blog 