Resource Manager
Resource Manager is an Enterprise Edition feature that provides a set of PL/SQL APIs that allow the DBA to assign a priority to sessions, making sure that the most important transactions get the major share of system resources. In this article I'll present a simple example for a hydrid system that comprises of high priority web based OLTP transactions and low priority batch processing
First we create a web and a batch user for the test.
CREATE USER web_user identified by web_user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CONNECT TO web_user;
CREATE USER batch_user identified by batch_user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CONNECT TO batch_user;
In order to set up a resource plan a pending area has to be created. This is simply a working area where the plan can be defined and validated before it is applied to the server. The following examples show a breakdown of setting up the resource plan, ending with the complete plan definition. Remember, only a complete and valid plan can be applied to the server so don't try and run these commands individually.
First we create a pending area.
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area();
DBMS_RESOURCE_MANAGER.create_pending_area();
END;
/
Next we create a plan.
BEGIN
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'hybrid_plan',
comment => 'Plan for a combination of high and low priority tasks.');
END;
/
Then we create a web and a batch consumer group.
BEGIN
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'WEB_CG',
comment => 'Web based OTLP processing - high priority');
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'BATCH_CG',
comment => 'Batch processing - low priority');
END;
/
Then we assign the consumer groups to the plan and indicate their relative priority, remembering to add the OTHER_GROUPS plan directive.
BEGIN
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'hybrid_plan',
group_or_subplan => 'web_cg',
comment => 'High Priority',
cpu_p1 => 80,
cpu_p2 => 0,
parallel_degree_limit_p1 => 4);
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'hybrid_plan',
group_or_subplan => 'batch_cg',
comment => 'Low Priority',
cpu_p1 => 0,
cpu_p2 => 80,
parallel_degree_limit_p1 => 4);
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan => 'hybrid_plan',
group_or_subplan => 'OTHER_GROUPS',
comment => 'all other users - level 3',
cpu_p1 => 0,
cpu_p2 => 0,
cpu_p3 => 100);
END;
/
Finally we validate and apply the resource plan.
BEGIN
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
To define the complete plan we do something like this.
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area();
DBMS_RESOURCE_MANAGER.create_pending_area();
-- Create plan
DBMS_RESOURCE_MANAGER.create_plan(
plan => 'hybrid_plan',
comment => 'Plan for a combination of high and low priority tasks.');
-- Create consumer groups
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'web_cg',
comment => 'Web based OTLP processing - high priority');
DBMS_RESOURCE_MANAGER.create_consumer_group(
consumer_group => 'batch_cg',
comment => 'Batch processing - low priority');
-- Assign consumer groups to plan and define priorities
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'hybrid_plan',
group_or_subplan => 'web_cg',
comment => 'High Priority - level 1',
cpu_p1 => 100,
cpu_p2 => 0,
cpu_p3 => 0);
DBMS_RESOURCE_MANAGER.create_plan_directive (
plan => 'hybrid_plan',
group_or_subplan => 'batch_cg',
comment => 'Low Priority - level 2',
cpu_p1 => 0,
cpu_p2 => 100,
cpu_p3 => 0);
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan => 'hybrid_plan',
group_or_subplan => 'OTHER_GROUPS',
comment => 'all other users - level 3',
cpu_p1 => 0,
cpu_p2 => 0,
cpu_p3 => 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area();
END;
/
Now that the plan has been defined and applied to the server we can assign our users to individual consumer groups. A session can be manually switched between consumer groups it has been assigned to, but in the example below we set the default consumer group and assume the session will remain with this for it's lifetime.
BEGIN
-- Assign users to consumer groups
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'web_user',
consumer_group => 'web_cg',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'batch_user',
consumer_group => 'batch_cg',
grant_option => FALSE);
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('web_user', 'web_cg');
DBMS_RESOURCE_MANAGER.set_initial_consumer_group('batch_user', 'batch_cg');
END;
/
The
RESOURCE_MANAGER_PLAN parameter is used to tell the instance which resource plan to use. This can be set in the init.ora file or by using the ALTER SYSTEM command.ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = hybrid_plan;
We can now see that sessions connecting via these users are assigned to the correct consumer group.
sys:tsh1> SELECT username, resource_consumer_group
2 FROM v$session
3 WHERE username IN ('WEB_USER','BATCH_USER');
USERNAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
BATCH_USER BATCH_CG
WEB_USER WEB_CG
2 rows selected.
sys:tsh1>
Alternatively you could use a single user and switch the consumer group for the current session depending on the type of processing being done. Assuming the user has been assigned the switch privilege for the consumer group this switch is done as follows.
DECLARE
v_old_cg VARCHAR2(30);
BEGIN
DBMS_SESSION.switch_current_consumer_group (
new_consumer_group => 'BATCH_CG',
old_consumer_group => v_old_cg,
initial_group_on_error => FALSE);
END;
/
No comments:
Post a Comment