Tag Archives: dbrm

Little things worth knowing: scheduler jobs and Instance Caging

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.

Limiting the Degree of Parallelism via Resource Manager and a gotcha

This might be something very obvious for the reader but I had an interesting┬árevelation┬árecently when implementing parallel_degree_limit_p1 in a resource consumer group. My aim was to prevent users mapped to a resource consumer group from executing any query in parallel. The environment is fictional, but let’s assume that it is possible that maintenance operations for example leave indexes and tables decorated with a parallel x attribute. Another common case is the restriction of PQ resource to users to prevent them from using all the machine’s resources.

This can happen when you perform an index rebuild for example in parallel to speed the operation up. However the DOP will stay the same with the index after the maintenance operation, and you have to explicitly set it back:

SQL> alter index I_T1$ID1 rebuild parallel 4;

Index altered.

SQL> select index_name,degree from ind where index_name = 'I_T1$ID1';

INDEX_NAME		       DEGREE
------------------------------ ----------------------------------------
I_T1$ID1		       4

SQL>

Continue reading