Table of Contents
This chapter describes the basic concepts of DBMS_RESOURCE_MANAGER package and how to use its procedures.
DBMS_RESOURCE_MANAGER can be used in Tibero to allocate resources by grouping tasks according to their characteristics. For example, in situations where OLTP and batch tasks are processed concurrently with limited number of resources, more resources can be allocated to OLTP processing to enhance efficiency.
DBMS_RESOURCE_MANAGER package allows reallocation of resources, such as CPU, that are required for task processing based on task characteristics in order to increase efficiency. The procedures in the DBMS_RESOURCE_MANAGER package can be used to add a RESOURCE PLAN to the database, and configure and apply an appropriate resource allocation method.
The following are the specifications of the DBMS_RESOURCE_MANAGER package.
USE_RESOURCE_MANAGER parameter must be set to Y to use the package.
DBA privilege is required to use the DBMS_SCHEDULER package, and only the owner of the RSRC plan can execute or update the plan.
CONSUMER GROUPS, CONSUMER GROUP MAPPINGS, and RSRC PLAN DIRECTIVES, which are used to decide the resource allocation method, can be added.
The following views can be used to display information about the RSRC PLANS, CONSUMER GROUPS, and RSRC PLAN DIRECTIVES that have been added to the current database.
DBA_RSRC_PLANS DBA_RSRC_CONSUMER_GROUPS DBA_RSRC_PLAN_DIRECTIVES
The following view can be used to display the RSRC PLAN modification history recorded along with a PLAN name and time information.
DBA_RSRC_AUDIT
The following views can be used to display information about ACTIVE PLAN related items including RSRC PLANS, CONSUMER GROUPS, and SESSIONS.
V$SESSION V$RSRC_PLAN V$RSRC_CONSUMER_GROUPS V$RSRC_SESSION_INFO
Multiple RSRC PLANS, CONSUMER GROUPS, and RSRC PLAN DIRECTIVES can be added, but only a single plan can be applied.
If no RSRC PLAN has been applied, the DEFAULT RESOURCE PLAN that contains the DEFAULT CONSUMER GROUP is applied.
Sessions that are not included in any CONSUMER GROUP are included in DEFAULT CONSUMER GROUP when applying a PLAN.
The total usage of CPU for all CONSUMER GROUPS must be less than 100% in order to allocate minimum CPU resource to DEFAULT CONSUMER GROUP.
This section describes the procedures of the DBMS_RESOURCE_MANAGER package in alphabetical order.
Creates a new CONSUMER GROUP.
Details about the CREATE_CONSUMER_GROUP procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
(
consumer_group IN VARCHAR2,
category IN VARCHAR2,
comments IN VARCHAR2
);
Parameters
| Parameters | Description |
|---|---|
| consumer_group | CONSUMER GROUP name. Name must be unique. |
| category | CATEGORY that includes the CONSUMER GROUP. |
| comments | Additional information, such as purpose, about the CONSUMER GROUP. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
consumer_group => 'group s',
category => 'default',
comments => 'consumer group for sys users');
END;
/
Creates a new RESOURCE PLAN.
Details about the CREATE_PLAN procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.CREATE_PLAN
(
plan IN VARCHAR2,
comments IN VARCHAR2
);
Parameters
| Parameter | Description |
|---|---|
| plan | RESOURCE PLAN name. Name must be unique. |
| comments | Additional information, such as purpose, about the RESOURCE PLAN. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan => 'plan t',
comments => 'resource plan for tibero');
END;
/
Creates a new RESOURCE PLAN DIRECTIVE.
Details about the CREATE_PLAN_DIRECTIVE procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
(
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2,
comments IN VARCHAR2,
mgmt_p1 IN NUMBER DEFAULT NULL
);
Parameters
| Parameter | Description |
|---|---|
| plan | Name of the RESOURCE PLAN that contains the PLAN DIRECTIVE. |
| group_or_subplan | Name of the CONSUMER GROUP or SUBPLAN of the PLAN DIRECTIVE. |
| comments | Additional information, such as purpose, about the PLAN DIRECTIVE. |
| mgmt_p1 | Target CPU usage percentage of the PLAN DIRECTIVE. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'plan t',
group_or_subplan => 'group s',
comments => 'plan directive for sys users',
mgmt_p1 => 60);
END;
/
Creates a simple RESOURCE PLAN that contains up to eight CONSUMER GROUPS and their target CPU usage in a single execution.
Details about the CREATE_SIMPLE_PLAN procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN
(
simple_plan IN VARCHAR2 DEFAULT NULL,
consumer_group1 IN VARCHAR DEFAULT NULL,
consumer_group2 IN VARCHAR DEFAULT NULL,
consumer_group3 IN VARCHAR DEFAULT NULL,
consumer_group4 IN VARCHAR DEFAULT NULL,
consumer_group5 IN VARCHAR DEFAULT NULL,
consumer_group6 IN VARCHAR DEFAULT NULL,
consumer_group7 IN VARCHAR DEFAULT NULL,
consumer_group8 IN VARCHAR DEFAULT NULL,
group1_percent IN NUMBER DEFAULT NULL,
group2_percent IN NUMBER DEFAULT NULL,
group3_percent IN NUMBER DEFAULT NULL,
group4_percent IN NUMBER DEFAULT NULL,
group5_percent IN NUMBER DEFAULT NULL,
group6_percent IN NUMBER DEFAULT NULL,
group7_percent IN NUMBER DEFAULT NULL,
group8_percent IN NUMBER DEFAULT NULL
);
Parameters
| Parameter | Description |
|---|---|
| simple_plan | RESOURCE PLAN name. Name must be unique. |
| consumer_group1 | Name of the first CONSUMER GROUP (must be unique). |
| consumer_group2 | Name of the second CONSUMER GROUP (must be unique). |
| consumer_group3 | Name of the third CONSUMER GROUP (must be unique). |
| consumer_group4 | Name of the fourth CONSUMER GROUP (must be unique). |
| consumer_group5 | Name of the fifth CONSUMER GROUP (must be unique). |
| consumer_group6 | Name of the sixth CONSUMER GROUP (must be unique). |
| consumer_group7 | Name of the seventh CONSUMER GROUP (must be unique). |
| consumer_group8 | Name of the eighth CONSUMER GROUP (must be unique). |
| group1_percent | Target CPU usage percentage for group 1. |
| group2_percent | Target CPU usage percentage for group 2. |
| group3_percent | Target CPU usage percentage for group 3. |
| group4_percent | Target CPU usage percentage for group 4. |
| group5_percent | Target CPU usage percentage for group 5. |
| group6_percent | Target CPU usage percentage for group 6. |
| group7_percent | Target CPU usage percentage for group 7. |
| group8_percent | Target CPU usage percentage for group 8. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(
simple_plan => 'plan s',
consumer_group 1 => 'group 1',
consumer_group 2 => 'group 2',
consumer_group 3 => 'group 3',
consumer_group 4 => 'group 4',
consumer_group 5 => 'group 5',
consumer_group 6 => 'group 6',
consumer_group 7 => 'group 7',
consumer_group 8 => 'group 8',
group1_percent => '20',
group1_percent => '20',
group1_percent => '10',
group1_percent => '10',
group1_percent => '10',
group1_percent => '10',
group1_percent => '10',
group1_percent => '10');
END;
/
Deletes an existing CONSUMER GROUP.
Details about the DELETE_CONSUMER_GROUP procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP
(
consumer_group IN VARCHAR2
);
Parameters
| Parameter | Description |
|---|---|
| consumer_group | CONSUMER GROUP name. Name must exist. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.DELETE_CONSUMER_GROUP(
consumer_group => 'group s');
END;
/
Deletes an existing RESOURCE PLAN.
Details about the DELETE_PLAN procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.DELETE_PLAN
(
plan IN VARCHAR2
);
Parameters
| Parameter | Description |
|---|---|
| plan | RESOURCE PLAN name. Name must exist. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.DELETE_PLAN(
plan => 'plan t');
END;
/
Deletes an existing PLAN DIRECTIVE.
Details about the DELETE_PLAN_DIRECTIVE procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE
(
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2
);
Parameters
| Parameter | Description |
|---|---|
| plan | RESOURCE PLAN name of the PLAN DIRECTIVE. Name must exist. |
| group_or_subplan | CONSUMER GROUP or SUBPLAN of the PLAN DIRECTIVE. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.DELETE_PLAN_DIRECTIVE(
plan => 'plan t',
group_or_subplan => 'group s');
END;
/
Sets a MAPPING RULE between an existing consumer group and a session.
Details about the SET_CONSUMER_GROUP_MAPPING procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(
attribute IN VARCHAR2,
value IN VARCHAR2,
consumer_group IN VARCHAR2 DEFAULT NULL
);
Parameters
| Parameter | Description |
|---|---|
| attribute | Attribute for the mapping. |
| value | Attribute value for the mapping. |
| consumer_group | Name of the consumer group to map to. |
Supported attributes
| attribute | Description |
|---|---|
| MODULE_NAME_ACTION | The session with the same module name and action items is mapped to the specified consumer group. |
| MODULE_NAME | The session with the same module name item is mapped to the specified consumer group. |
| DATABASE_USER | The session with the same database user item is mapped to the specified consumer group. |
| CLIENT_PROGRAM | The session with the same program item is mapped to the specified consumer group. |
| CLIENT_OS_USER | The session with the same OS user item is mapped to the specified consumer group. |
| CLIENT_MACHINE | The session with the same machine item is mapped to the specified consumer group. |
| CLIENT_ID | The session with the same client identifier item is mapped to the specified consumer group. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
attribute => 'database_user',
value => 'sys',
consumer_group => 'group s');
END;
/
Sets an existing RESOURCE PLAN in the database.
Details about the SWITCH_PLAN procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.SWITCH_PLAN
(
switch_plan IN VARCAHR2
);
Parameters
| Parameter | Description |
|---|---|
| switch_plan | RESOURCE PLAN name. Plan must exist. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.SWITCH_PLAN(
switch_plan => 'plan t');
END;
/
Modifies an existing CONSUMER GROUP.
Details about the UPDATE_CONSUMER_GROUP procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP
(
consumer_group IN VARCHAR2,
new_category IN VARCHAR2 DEFAULT NULL,
new_comments IN VARCHAR2 DEFAULT NULL
);
Parameters
| Parameter | Description |
|---|---|
| consumer_group | CONSUMER GROUP name. Plan must exist. |
| new_category | CATEGORY that includes the CONSUMER GROUP. |
| new_comments | Additional information, such as purpose, about the CONSUMER GROUP. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_CONSUMER_GROUP(
consumer_group => 'group s',
new_cateogry => 'default',
new_comments => 'consumer group for sys users');
END;
/
Modifies an existing RESOURCE _PLAN.
Details about the UPDATE_PLAN procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.UPDATE_PLAN
(
plan IN VARCHAR2,
new_comments IN VARCHAR2 DEFAULT NULL
);
Parameters
| Parameter | Description |
|---|---|
| plan | RESOURCE PLAN name. Plan must exist. |
| new_comments | Additional information, such as purpose, about the RESOURCE PLAN. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_PLAN(
plan => 'plan t',
new_comments => 'resource plan for tibero');
END;
/
Modifies an existing PLAN_DIRECTIVE.
Details about the UPDATE_PLAN_DIRECTIVE procedure are as follows:
Prototype
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE
(
plan IN VARCHAR2,
group_or_subplan IN VARCHAR2,
new_comments IN VARCHAR2 DEFAULT NULL,
mgmt_p1 IN NUMBER DEFAULT NULL
);
Parameters
| Parameter | Description |
|---|---|
| plan | Name of the RESOURCE PLAN of the PLAN DIRECTIVE. |
| group_or_subplan | Name of the CONSUMER GROUP or SUBPLAN of the PLAN DIRECTIVE. |
| new_comments | Additional information, such as purpose, about the PLAN DIRECTIVE. |
| mgmt_p1 | Target CPU usage level (in percent) of the PLAN DIRECTIVE. |
Example
BEGIN
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(
plan => 'plan t',
group_or_subplan => 'group s',
new_comments => 'plan directive for sys users',
mgmt_p1 => 60);
END;
/