With the consumer groups present, a resource
plan can be created using the
create_plan procedure, and it can be
associated to the consumer groups using the
create_plan_directive
procedure.
PROCEDURE create_plan (
plan
IN
VARCHAR2,
comment
IN
VARCHAR2,
cpu_mth
IN
VARCHAR2 DEFAULT 'EMPHASIS',
active_sess_pool_mth
IN
VARCHAR2 DEFAULT
'ACTIVE_SESS_POOL_ABSOLUTE',
parallel_degree_limit_mth IN
VARCHAR2 DEFAULT
'PARALLEL_DEGREE_LIMIT_ABSOLUTE',
queueing_mth
IN
VARCHAR2 DEFAULT 'FIFO_TIMEOUT')
PROCEDURE
create_plan_directive (
plan
IN
VARCHAR2,
group_or_subplan
IN
VARCHAR2,
comment
IN
VARCHAR2,
cpu_p1
IN
NUMBER DEFAULT NULL,
cpu_p2
IN
NUMBER DEFAULT NULL,
cpu_p3
IN
NUMBER DEFAULT NULL,
cpu_p4
IN
NUMBER DEFAULT NULL,
cpu_p5
IN
NUMBER DEFAULT NULL,
cpu_p6
IN
NUMBER DEFAULT NULL,
cpu_p7
IN
NUMBER DEFAULT NULL,
cpu_p8
IN
NUMBER DEFAULT NULL,
active_sess_pool_p1
IN
NUMBER DEFAULT NULL,
queueing_p1
IN
NUMBER DEFAULT NULL,
parallel_degree_limit_p1
IN
NUMBER DEFAULT NULL,
switch_group
IN
VARCHAR2 DEFAULT NULL,
switch_time
IN
NUMBER DEFAULT NULL,
switch_estimate
IN
BOOLEAN DEFAULT FALSE,
max_est_exec_time
IN
NUMBER DEFAULT NULL,
undo_pool
IN
NUMBER DEFAULT NULL,
max_idle_time
IN
NUMBER DEFAULT NULL,
max_idle_blocker_time
IN
NUMBER DEFAULT NULL,
switch_time_in_call
IN
NUMBER DEFAULT NULL)
The
day_plan.sql
script uses these procedures to create a
resource plan suitable for daytime processing.
The OLTP
operations are associated with 80% of the CPU on
level one while batch operations receive 100% of
the remaining CPU at level two.
The
switch_group
and
switch_time
parameters are used in the OLTP plan directive
to specify that OLTP processes lasting more than
60 seconds should be switched to the batch
consumer group.
The
other_groups consumer group must be
included in any valid plan as it provides
resource allocation information for any
processes that are not explicitly associated
with the consumer groups.
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
-- Create a new plan
DBMS_RESOURCE_MANAGER.create_plan(
plan
=> 'day_plan',
comment => 'Plan suitable for daytime
processing.');
-- Assign consumer groups to plan and define
priorities
DBMS_RESOURCE_MANAGER.create_plan_directive
(
plan
=> 'day_plan',
group_or_subplan => 'oltp_consumer_group',
comment
=> 'Give OLTP processes higher
priority - level 1',
cpu_p1
=> 80,
switch_group
=> 'batch_consumer_group',
switch_time
=> 60);
DBMS_RESOURCE_MANAGER.create_plan_directive
(
plan
=> 'day_plan',
group_or_subplan => 'batch_consumer_group',
comment
=> 'Give batch processes lower
priority - level 2',
cpu_p2
=> 100);
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan
=> 'day_plan',
group_or_subplan => 'OTHER_GROUPS',
comment
=> 'all other users - level 3',
cpu_p3
=> 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
The
night_plan.sql script
creates a resource plan suitable for nighttime
processing in which the resource allocation is
the reverse of the daytime processing, such that
batch processes receive 80% of the CPU at level
one and OLTP
operations receive 100% of the remaining CPU at
level two.
Once again, the
other_groups
consumer group is specified as a catch-all.
BEGIN
DBMS_RESOURCE_MANAGER.clear_pending_area;
DBMS_RESOURCE_MANAGER.create_pending_area;
-- Create a new plan
DBMS_RESOURCE_MANAGER.create_plan(
plan
=> 'night_plan',
comment => 'Plan suitable for daytime
processing.');
-- Assign consumer groups to plan and define
priorities
DBMS_RESOURCE_MANAGER.create_plan_directive
(
plan
=> 'night_plan',
group_or_subplan => 'batch_consumer_group',
comment
=> 'Give batch processes lower
priority - level 2',
cpu_p1
=> 80);
DBMS_RESOURCE_MANAGER.create_plan_directive
(
plan
=> 'night_plan',
group_or_subplan => 'oltp_consumer_group',
comment
=> 'Give OLTP processes higher
priority - level 1',
cpu_p2
=> 100);
DBMS_RESOURCE_MANAGER.create_plan_directive(
plan
=> 'night_plan',
group_or_subplan => 'OTHER_GROUPS',
comment
=> 'all other users - level 3',
cpu_p3
=> 100);
DBMS_RESOURCE_MANAGER.validate_pending_area;
DBMS_RESOURCE_MANAGER.submit_pending_area;
END;
/
The
resource_plan_directives.sql
script
uses the
dba_rsrc_plan_directives
view to display information about the resource
plans currently defined on the system.
resource_plan_directives.sql
select
plan,
group_or_subplan,
status
from
dba_rsrc_plan_directives
order by
plan,
group_or_subplan;
The output from the
resource_plan_directives.sql
script is displayed below.
SQL> @resource_plan_directives.sql
PLAN
GROUP_OR_SUBPLAN
STATUS
------------------------------
------------------------------ ------
DAY_PLAN
BATCH
_CONSUMER_GROUP
ACTIVE
DAY_PLAN
OLTP_CONSUMER_GROUP
ACTIVE
DAY_PLAN
OTHER_GROUPS
ACTIVE
INTERNAL_PLAN
OTHER_GROUPS
ACTIVE
INTERNAL_QUIESCE
OTHER_GROUPS
ACTIVE
INTERNAL_QUIESCE
SYS_GROUP
ACTIVE
NIGHT_PLAN
BATCH
_CONSUMER_GROUP
ACTIVE
NIGHT_PLAN
OLTP_CONSUMER_GROUP
ACTIVE
NIGHT_PLAN
OTHER_GROUPS
ACTIVE
SYSTEM_PLAN
LOW_GROUP
ACTIVE
SYSTEM_PLAN
OTHER_GROUPS
ACTIVE
SYSTEM_PLAN
SYS_GROUP
ACTIVE
The resource manager is only activated when a
default resource plan is assigned.
Only one resource plan can be active at
any given time.
Resource plan switches can be automated
using scheduler windows or performed manually by
setting the
resource_manager_plan
parameter using the
alter system command as shown below.
alter system set
resource_manager_plan = day_plan;
The currently active resource plan can be
identified by querying the
v$rsrc_plan
view as shown in the
active_plan.sql
script:
select
*
from
v$rsrc_plan;
The output from the
active_plan.sql
script is displayed:
NAME
IS_TO
-------------------------------- -----
DAY_PLAN
TRUE
