|
The
DBMS_RESOURCE_MANAGER package is used to
administer the new resource plan and
consumer group options in Oracle. The
package contains several procedures that are
used to create, modify, drop and grant
access to resource plans, groups, directives
and pending areas. The invoker must have the
ADMINISTER_RESOURCE_MANAGER system privilege
to execute these procedures. The procedures
to grant and revoke this privilege are in
the package DBMS_RESOURCE_MANAGER_PRIVS.
The
calling syntax for all of the
DBMS_RESOURCE_MANAGER packages follow.
DBMS_RESOURCE_MANAGER.CREATE_PLAN (
plan IN VARCHAR2,
comment IN VARCHAR2,
cpu_mth IN VARCHAR2
DEFAULT 'EMPHASIS',
max_active_sess_target_mth IN VARCHAR2
DEFAULT
'MAX_ACTIVE_SESS_ABSOLUTE',
parallel_degree_limit_mth IN VARCHAR2
DEFAULT
'PARALLEL_DEGREE_LIMIT_ABSOLUTE');
Where:
-
Plan - the plan name
-
Comment - any text comment you
want associated with the plan name
-
Cpu_mth - one of
EMPHASIS or ROUND-ROBIN
-
max_active_sess_target_mth -
allocation method for max. active
sessions
-
parallel_degree_limit_mth -
allocation method for degree of
parallelism
Consumer Group Mappings Using
Oracle dbms_resource_manager
There is a new procedure called
set_group_mapping within the Oracle
dbms_resource_manager that helps to map the
session attributes to a consumer group.
These attributes are of two types: login
attributes and runtime attributes. Here are
some examples using the Oracle
dbms_resource_manager.
How to use these mappings to set the
consumer groups automatically.
DBMS_RESOURCE_MANAGER Examples:
The following statement sets the user
‘scott’ to map to the resource group ‘legal’
whenever he logs in:
EXEC DBMS_RESOURCE_MANAGER.
SET_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER. ORACLE_USER, 'scott',
'legal’);
The statement below maps the user with
module name ‘ENQUIRY’ to the resource group
‘AUTOMOB’ whenever the module activates:
EXEC DBMS_RESOURCE_MANAGER.
SET_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER. MODULE_NAME, 'enquiry','automob’);
The
requirements for a valid resource plan are
outlined in the definition of the
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA
procedure below. The first package listed,
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP
must be run the first time a user is
assigned to a resource group or you won't be
able to assign the user to the group.
set echo on
spool test_resource_plan.doc
-- Grant system privilege to plan
administrator
--
execute
dbms_resource_manager_privs.grant_system_privilege('SYSTEM','ADMINISTER_RESOURCE_MANAGER',TRUE);
--
--connect to plan administrator
--
CONNECT
system/system_test@ortest1.world
--
-- Create Plan Pending Area
--
EXECUTE
dbms_resource_manager.create_pending_area();
--
-- Create plan
--
execute
dbms_resource_manager.create_plan('MASTER','Example
Resource Plan','EMPHASIS');
execute
dbms_resource_manager.create_plan('USERS','Example
Resource Sub Plan','EMPHASIS');
execute
dbms_resource_manager.create_plan('REPORTS','Example
Resource Sub Plan','EMPHASIS');
--
--Create tiers of groups in plan
--
EXECUTE
dbms_resource_manager.create_consumer_group('ONLINE_USERS','3rd
level group','ROUND-ROBIN');
EXECUTE
dbms_resource_manager.create_consumer_group('BATCH_USERS','3rd
level group','ROUND-ROBIN');
EXECUTE
dbms_resource_manager.create_consumer_group('ONLINE_REPORTS','2rd
level group','ROUND-ROBIN');
EXECUTE
dbms_resource_manager.create_consumer_group('BATCH_REPORTS','2rd
level group','ROUND-ROBIN');
--
-- Create plan directives
--
EXECUTE
dbms_resource_manager.create_plan_directive('MASTER',
'USERS',
0,60,0,0,0,0,0,0,NULL);
EXECUTE
dbms_resource_manager.create_plan_directive('MASTER',
'REPORTS',
0,20,0,0,0,0,0,0,NULL);
EXECUTE
dbms_resource_manager.create_plan_directive('MASTER','OTHER_GROUPS',
0,20,0,0,0,0,0,0,NULL);
EXECUTE
dbms_resource_manager.create_plan_directive('USERS',
'ONLINE_USERS',
0,0,70,0,0,0,0,0,NULL);
EXECUTE
dbms_resource_manager.create_plan_directive('USERS',
'BATCH_USERS', 0,0,30,0,0,0,0,0,NULL);
EXECUTE
dbms_resource_manager.create_plan_directive('REPORTS','ONLINE_REPORTS',0,0,70,0,0,0,0,0,NULL);
EXECUTE
dbms_resource_manager.create_plan_directive('REPORTS','BATCH_REPORTS',
0,0,30,0,0,0,0,0,NULL);
--
-- Verify Plan
--
EXECUTE
dbms_resource_manager.validate_pending_area;
--
-- Submit Plan
--
EXECUTE
dbms_resource_manager.submit_pending_area;
spool off
set echo off
For more
details on dbms_resource_manager, see the "Easy
Oracle Jumpstart" by Robert Freeman and
Steve Karam.
My related
notes on dbms_resource_manager:
|
