19. Dbms_resource_manager (for maintaining resource plans, resource usage groups, and resource planning directives)

1. Overview

Role: Used to maintain resource plans, resource usage groups, and resource plan directives, and package dbms_resource_manager_privs is used to maintain permissions related to resource management.

2, the composition of the package

1), Dbms_resource_manager.create_plan
Role: Establish a resource plan
Syntax: Dbms_resource_manager.create_plan (Plan in varchar2,comment with varchar2,cpu_mth in varchar2 default ' emphasis ',
active_sess_pool_mth in varchar2 default ' Active_sess_pool_absolute ',
parallele_degree_limit_mth in varchar2 default ' Parallel_degree_limit_absolute ',
queueing_mth in varchar2 default ' Fifo_timeout ');
Where plan specifies the resource plan name, comment Specifies the user annotation information, cpu_mth specifies the allocation method for the CPU resource, active_sess_pool_mth specifies the allocation method for the maximum active session,
PARALLELE_DEGREE_LIMIT_MTH specifies the allocation method for the degree of parallelism, queueing_mth specifies the type of queue policy for the active session pool.

2), Dbms_resource_manager.create_simple_plan
Role: Used to establish a simple resource plan that contains up to 8 resource usage groups.
Syntax: Dbms_resource_manager.create_simple_plan (Simple_plan in varchar2 default,
Consumer_group1 in VARCHAR2 default,group1_cpu in number default,
Consumer_group2 in VARCHAR2 default,group2_cpu in number default,
Consumer_group3 in VARCHAR2 default,group3_cpu in number default,
Consumer_group4 in VARCHAR2 default,group4_cpu in number default,
CONSUMER_GROUP5 in VARCHAR2 default,group5_cpu in number default,
Consumer_group6 in VARCHAR2 default,group6_cpu in number default,
CONSUMER_GROUP7 in VARCHAR2 default,group7_cpu in number default,
Consumer_group8 in VARCHAR2 default,group8_cpu in number default);

3), Dbms_resource_manager.update_plan
Role: Used to update the definition of a resource plan
Syntax: Dbms_resource_manager.update_plan (Plan in Varchar2,
New_comment in varchar2 default null,new_cpu_mth in varchar2 default NULL,
new_active_sess_pool_mth in varchar2 default null,new_parallel_degree_limit_mth in varchar2 default NULL,
NEW_QUEUEING_MTH in varchar2 default null);
Where new_comment specifies the user's new annotation information, new_cpu_mth specifies the new allocation method for the CPU resource,
NEW_ACTIVE_SESS_POOL_MTH Specifies the new allocation method for the maximum active session,
New_parallel_degree_limit_mth a new allocation method that specifies the degree of parallelism,
NEW_QUEUEING_MTH Specifies the new queue policy type for the active session pool.

4), Dbms_resource_manager.delete_plan
Role: Used to delete a resource plan
Syntax: Dbms_resource_manager.delete_plan (plan in varchar2);

5), Dbms_resource_manager.delete_plan_cascade
Role: Used to delete a resource plan and all its descendants (Resource plan directives, sub-plans, and resource usage groups)
Syntax: Dbms_resource_manager.delete_plan_cascade (plan in varchar2);

6), Dbms_resource_manager.greate_consumer_group
Role: Used to establish resource usage groups
Syntax: Dbms_resource_manager.greate_consumer_group (Consumer_group in Varchar2,
Comment in varchar2,cpu_mth in varchar2 default ' Round-robin ');
Where consumer_group specifies that the resource uses the group name.

7), Dbms_resource_manager.update_consumer_group
Role: Used to update Resource Usage Group information
Syntax: Dbms_resource_manager.update_consumer_group (
Consumer_group in Varcahr2,new_comment in varchar2,new_cpu_mth in varchar2 default null);

8), Dbms_resource_manager.delete_consumer_group
Role: Delete Resource usage groups
Syntax: Dbms_resource_manager.delete_consumer_group (Consumer_group in varchar2);

9), dbms_resource_manager.create_plan_directive
Role: Establish resource planning directives
Syntax: dbms_resource_manager.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 unlimited,
QUEUEING_P1 in number Default unlimited,
Switch_group in varchar2 default NULL,
Switch_time in number Default unlimited,
Switch_estimate in Boolean default false,
Max_est_exec_time in number default nulimited,
Undo_pool in number Default unlimited,
PARALLE1_DEGREE_LIMIT_P1 in number default unlimited);
Where Group_or_subplan specifies the name of a resource using a group or a child plan, CPU_P1 specifies the first parameter of the CPU resource allocation method (P2 is the second parameter ...). )，
ACTIVE_SESS_POOL_P1 Specifies the first parameter of the maximum active session allocation method, QUEUEING_P1 specifies the queue time-out,
SWITCH_GROUP Specifies the Resource Usage group to switch to when the switching time is reached, switch_time specifies the switching time,
Switch_estimate default False, when set to True, notifies Oracle to use the execution time estimate to automatically switch resource usage groups.
UNDO_POOL Specifies the size of the undo pool for a resource using a group, PARALLE1_DEGREE_LIMIT_P1 specifies the first parameter of the parallelism allocation method.

10), Dbms_resource_manager.update_plan_directive
Role: Used to update resource plan directives
Syntax: dbms_resource_manager.update_plan_directive (
Plan in Varchar2,group_or_subplan in Varchar2,
New_comment in VARCHAR2,CPU_P1 in number default NULL,
NEW_CPU_P2 in number default NULL,NEW_CPU_P3 in number default NULL,
NEW_CPU_P4 in number default NULL,NEW_CPU_P5 in number default NULL,
NEW_CPU_P6 in number default NULL,NEW_CPU_P7 in number default NULL,
NEW_CPU_P8 in number default NULL,
NEW_ACTIVE_SESS_POOL_P1 in number Default unlimited,
NEW_QUEUEING_P1 in number Default unlimited,
New_switch_group in varchar2 default NULL,
New_switch_time in number Default unlimited,
New_switch_estimate in Boolean default false,
New_max_est_exec_time in number default nulimited,
New_undo_pool in number Default unlimited,
NEW_PARALLE1_DEGREE_LIMIT_P1 in number default unlimited);
Where NEW_CPU_P1 specifies the first parameter of the CPU resource allocation method (P2 is the second parameter ...). )，
NEW_ACTIVE_SESS_POOL_P1 Specifies the first parameter of the maximum active session allocation method, NEW_QUEUEING_P1 specifies the queue time-out,
NEW_SWITCH_GROUP Specifies the Resource Usage group to switch to when the switching time is reached, new_switch_time specifies the switching time,
New_switch_estimate default False, when set to True, notifies Oracle to use the execution time estimate to automatically switch resource usage groups.
NEW_UNDO_POOL Specifies the size of the undo pool for a resource using a group, NEW_PARALLE1_DEGREE_LIMIT_P1 specifies the first parameter of the parallelism allocation method.

11), Dbms_resource_manager.delete_plan_directive
Role: Used to delete resource plan directives
Syntax: dbms_resource_manager.delete_plan_directive (Plan in Varchr2,group_or_subplan in VARCHAR2);

12), Dbms_resource_manager.create_pending_area
Function: Used to establish the pending memory area, and the memory area will be used to change the resource management object
Syntax: Dbms_resource_manager.create_pending_area;

13), Dbms_resource_manager.validate_pending_area
Role: Used to verify changes in the resource manager
Syntax: Dbms_resource_manager.validate_pending_area;

14), Dbms_resource_manager.clear_pending_area
Role: Used to clear changes to the resource manager
Syntax: Dbms_resource_manager.clear_pending_area;

15), Dbms_resource_manager.submit_pending_area
Role: Used to commit changes to the resource manager
Syntax: Dbms_resource_manager.submit_pending_area;

16), Dbms_resource_manager. Set_initial_consumer_group
Role: The initial Resource Usage group for the specified user
Syntax: Dbms_resource_manager.set_initial_consumer_group (user in Varchar2,consumer_group in varchar2);
Where user specifies the username, consumer_group specifies the user's initial resource using the group name.

17), Dbms_resource_manager.switch_consumer_group_for_sess
Function: Dbms_resource_manager.switch_consumer_group_for_sess (session_id in number,session_serial in Number,consumer_ Group in VARCHR2);
Where session_id specifies the session ID number, session_serial specifies the session serial number.

18), Dbms_resource_manager.switch_consumer_group_for_user
Role: Resource Usage Group for changing all sessions for a specific user
Syntax: Dbms_resource_manager.switch_consumer_group_for_user (user in Varchar2,consumer_group in varchar2);

19), Dbms_resource_manager_privs.grant_system_privilege
Role: Used to grant resource management permissions to a user or role
Syntax: Dbms_resource_manager_privs.grant_system_privilege (grantee_name in Varchar2,
Privilege_name in varchar2 default ' Administer_resource_manager ', admin_option in Boolean);
Where grantee_name specifies the authorized user or role, Privilege_name specifies the resource management permissions to be granted.
ADMIN_OPTION Specifies whether resource management permissions can be delegated (true to Yes, false to not)
Example: Exec dbms_resource_manager_prive.grant_system_privilege (' Scott ', ' Administer_resource_manager ', true)

20), Dbms_resource_manager_privs.revoke_system_privilege
Role: Used to reclaim resource management permissions
Syntax: Dbms_resource_manager_privs. Revoke_system_privilege (Revokee_name in Varchar2,privilege_name in varchar2 default ' Administer_resource_manager ');
Where revoke_name specifies the user or role to which the permission is being retracted, Privilege_name specifies the resource management permissions to be retracted.
Example: Exec dbms_resource_manager_privs.revoke_system_privilege (' Scott ', ' Administer_resource_maneger ');

21), Dbms_resource_manager_privs.grant_switch_consumer_group
Role: Used to assign a user or role to a specific resource usage group
Syntax: Dbms_resource_manager_privs.grant_switch_consumer_group (grantee_name in Varchar2,consumer_group in Varchar2, Grant_option in Boolean);
Where grant_option specifies resources using Group delegation options
Example: Exec dbms_resource_manager_privs.grant_switch_consumer_group (' Scott ', ' Sys_group ', true)

22), Dbms_resource_manager_privs.revoke_switch_consumer_group
Role: Used to retract a resource usage group assigned to a user or role
Syntax: Dbms_resource_manager_privs.revoke_switch_consumer_group (Revokee in Varchar2,consumer_group in varchar2);
Example: Dbms_resource_manager_privs.revoke_switch_consumer_group (' Scott ', ' Sys_group ');

3. Comprehensive example

1), for the user authorized resources management rights
Description: By default, only privileged users SYS,DBA user system can manage resource management, and the rest of the users are authorized to use the license to Scott.
Conn System/[email protected]
EXEC dbms_resource_manager_prive.grant_system_privilege (' Scott ', ' Administer_resource_manager ', true)
2), set up various resource objects
Description: Before the resource object is established, the pending memory area must be allocated, and after the resource object is established, the pending memory area must be inspected and committed.
(1), establish pending memory area
Conn Scott/[email protected]
EXEC Dbms_resource_manager.create_pending_area
(2), establish resource use Group
exec dbms_resource_manager.create_consumer_group (' OLTP ', ' online transaction processing group ');
EXEC dbms_resource_manager.create_consumer_group (' DSS ', ' Decision Support Group ');
(3) Establishment of resource plans
EXEC Dbms_resource_manager.create_plan (' Day ', ' This resource is scheduled for online transaction processing ');
exec Dbms_resource_manager.create_plan (' Night ', ' This resource is planned for decision Support ');
(4) Establishment of resource planning directives
Description: Establishing a Resource plan directive must define a management relationship between the resource plan and the Other_groups group.
Example:
Begin
Dbms_resource_manager.create_plan_directive (plan=> ' Day ',group_or_subplan=> ' Sys_group ',comment=> ' highest level Group ', cpu_p1=>100,parallel_degree_limit_p1=>3);
Dbms_resource_manager.create_plan_directive (plan=> ' Day ',group_or_subplan=> ' olpt ',comment=> ' Intermediate Level Group ', CPU _P2=>80,PARALLEL_DEGREE_LIMIT_P1=>1);
Dbms_resource_manager.create_plan_directive (plan=> ' Day ',group_or_subplan=> ' other_groups ',comment=> ' Minimum Level Group ', cpu_p3=>80,parallel_degree_limit_p1=>1);
Dbms_resource_manager.create_plan_directive (plan=> ' Night ',group_or_subplan=> ' sys_group ',comment=> ' Highest Level group ', cpu_p1=>100,parallel_degree_limit_p1=>20);
Dbms_resource_manager.create_plan_directive (plan=> ' Night ',group_or_subplan=> ' DSS ',comment=> ' Intermediate Level Group ', CPU_P2=>80,PARALLEL_DEGREE_LIMIT_P1=>20);
Dbms_resource_manager.create_plan_directive (plan=> ' Night ',group_or_subplan=> ' other_groups ',comment=> ' Minimum Level Group ', cpu_p3=>80,parallel_degree_limit_p1=>20);
End
(5), verify pending memory area
Description: The validation pass allows the pending memory area to be submitted without the need to clear the pending memory area and reestablish the resource object.
EXEC Dbms_resource_manager.validate_pending_area
(6), submit pending memory area
Description: Commits the pending memory area and eventually establishes a permanent resource management object.
EXEC Dbms_resource_manager.submit_pending_area
3), assigning users to resource usage groups
EXEC dbms_resource_manager_privs.grant_switch_consumer_group (' Scott ', ' OLTP ', false);
EXEC dbms_resource_manager_privs.grant_switch_consumer_group (' Scott ', ' DSS ', false);
4), set the user's default resource usage group
Description: A database user can belong to more than one resource using a group, but only one resource can use the appropriate resource for a group at specific times at a particular session.
By setting the user's default resource usage group, you enable users to automatically use the resources of the group when they log on by using the appropriate resource.
EXEC dbms_resource_manager.set_initial_consumer_group (' Scott ', ' OLTP ');
5), activate the resource plan
Description: In order to restrict the use of a database user's resources through Database Explorer, you must activate the resource plan.
Alter system set Resource_manager_plan=day where Scope=momory.
6), change the session or user's Resource usage group
Description: If a user belongs to more than one resource using a group, the initial logon uses a default resource using the group, in order to change the Resource Usage group for a particular session,
Execute exec dbms_resource_manager.switch_consumer_group_for_sess (7,8, ' DSS ');
In order to change the Resource Usage group for all sessions of a particular user,
Execute exec dbms_resource_manager.switch_consumer_group_for_user (' Scott ', ' DSS ');

19, Dbms_resource_manager (used to maintain resource plans, resource usage groups, and resource plan directives)