Resource Manager – CPU allocation math – Part 2

As said in Part 1, the Part 2 will focus to measure how Oracle will effectively redistribute the CPU allocation defined in previous part. This is a important step while testing Resource Manager, it is very important to test your plans to ensure that Oracle behavior is according of what you are expecting.

Measure CPU allocation it is not a easy task, fortunately Oracle provide us some views related to Resource Manager to help us on this task.
First things first, after creating consumer groups, usernames, roles, plan and plan directives it is mandatory to tell Oracle what is the plan we are going to use. For that use the parameter resource_manager_plan

SQL> alter system set resource_manager_plan='DW_PLAN' scope=both;
System altered.

To measure the CPU allocation, it is necessary to create some heavy cpu loading tasks that use specified database services (as we defined as consumer group mapping). I’ve created 3 simple scripts that burn CPU for each service. The statement is a heavy CPU oriented as you can see bellow:

[oracle@phoenix resource_manager]$  cat cpu_alloc_burn.sql
SET TERMOUT OFF
select distinct t1.N2 from t1, t2
where t1.N1<>t2.N2
and t1.N3<>t2.N1
and t1.N2 <> t2.N1
and t2.N2 is not null;

To fire up the 20 sessions for each service. Bellow is an example for the database service S_DAILY_LOAD

echo "Starting 20 new executions in S_DAILY_LOAD service"
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &
sqlplus -s dw_user/dw_user@S_DAILY_LOAD @cpu_alloc_burn.sql &

[oracle@phoenix resource_manager]$  ./run_adhoc.sh
Starting 20 new executions for S_ADHOC service...
[oracle@phoenix resource_manager]$  ./run_daily_load.sh
Starting 20 new executions in S_DAILY_LOAD service
[oracle@phoenix resource_manager]$  ./run_reporting.sh
Starting 20 new executions for S_REPORTING service...

Now that we are burn heavily our cpu, let’s check Oracle view to ensure that session are in the correct resource consumer group.


SQL> select distinct username, resource_consumer_group, service_name from gv$session
where resource_consumer_group in ('ADHOC_QUERYS', 'DAILY_LOAD', 'REPORTING')
and status= 'ACTIVE'
order by resource_consumer_group;

USERNAME		       RESOURCE_CONSUMER_GROUP		SERVICE_NAME
------------------------------ -------------------------------- ----------------------------------------------------------------
DW_USER 		       ADHOC_QUERYS			S_ADHOC
DW_USER 		       DAILY_LOAD			S_DAILY_LOAD
DW_USER 		       REPORTING			S_REPORTING

Everything sounds good, now it is time to measure CPU activity based on Resource Manager view v$rsrc_consumer_group

SQL> SELECT name,
       active_sessions, execution_waiters, requests,
       cpu_wait_time, cpu_waits, consumed_cpu_time, yields
  FROM v$rsrc_consumer_group
ORDER BY cpu_wait_time;

Resource Manager view

Now it is time for math. The result of the previous query was taken about 5 minutes of running and the math shows us that Resource Manager is not yet “respecting” our CPU allocation according the numbers. As said before, we are probably unable to get a perfect match, but a close match.

Desired scenario:

Resource Manager Real CPU allocation

 

 

 

Real world scenario:
Resource Manager CPU math

 

To make sure that we can get better numbers i will let the sessions running under DBRM for several minutes, and little better numbers here, still not a perfect match between what we have defined in the first part.
As you can easily see, DAILY_LOAD is consuming 85% of our CPU time according Oracle vs 65% that we had specified. This is also happen with REPORTING a ADHOC_QUERYS with 12,75% and 11,83%.
Our conclusion is for a perfect match you need that consumer group to fully utilize its allocation. That will probably be very difficult. Please also note that for a much more complex plans (with sub plans as example) this task will be harder.

Update:

I’ve let the load scripts to run for about 1 hour to check if the results are more according what we expect from our resource manager CPU allocation. Bellow are the results (in SQL screenshot). The results are much better than the previous 5 minutes attempt:

Resource Manager CPU allocation 1 hour

 

The next part (Part 3) will be focused on what wait events are present in sessions when Oracle is managing your CPU allocation.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s