Oracle Database Resource Management

Oracle Database Resource Management
1. Understand Resource Manager terminology

Three basic structures of Resource Manager: Resource user group, Resource plan (and auxiliary plan), and Resource plan wizard.

The database resource manager can help you manage the way resources (such as CPU and restoration space) are allocated between user sessions. Follow these steps to configure the resource manager:
 
 

  
  
User Group defines the user group that groups user sessions based on resource processing requirements. The resource plan allocates resources to the user group. Specify users and roles. They can start sessions in each user group or switch their sessions to each user group.
  
  
User Group ing defines user group ing rules. This rule maps user sessions to user groups based on session attributes (such as user name, service name, and module name. To resolve the ing conflict, apply the ing rule in the order of priority of the ing rule.
  
  
A scheduler defines a resource scheduler, which contains instructions for allocating resources to a user group. For example, you need to specify the percentage of CPU resources allocated to each user group for each plan. You can choose to specify other restrictions, such as the maximum time that sessions in the user group can be executed or kept idle, or the maximum number of CPU or I/O resources consumed by a session before it is automatically switched to a low-priority user group.
  
  
Set the resource plan for viewing the current activity. Activate the resource plan.
  
  
Performance Statistics monitor the statistics of currently enabled resource plans. Monitor CPU and I/O usage by user group, and monitor the number of constraints executed by resource manager for CPU by user group.
 
 

2. Understand the Resource Manager Allocation Method

Resource Manager can allocate resources based on one or more measurement units:

• CPU usage
• Parallelism degree
• Number of active sessions
• Undo Space
• CPU time limit
• Idle Time Limit

3. Learn more about DEFAULT_PLAN

Resource plan resource_manager_plan parameters:

SQL> show parameter resourceNAME TYPE VALUE contains too many resource_limit boolean contains integer 2resource_manager_plan string -- set resource_manager_plan to DEFAULT_PLANSQL> alter system set resource_manager_plan = 'default _ plan'; System altered. SQL> show parameter resourceNAME TYPE VALUE exceed limit ------------------------------ resource_limit boolean FALSEresource_manager_cpu_allocation integer 2resource_manager_plan string DEFAULT_PLAN

If the value of resource_manager_plan is not set, resource management is not performed in the instance.

4. Create a resource plan

You can use CREATE_PLAN, UPDATE_PLAN, and DELETE_PLAN of the PL/SQL package "DBMS_RESOURCE_MANAGER" to create, update, and delete resource plans.

 

-- Create a waiting area for the Resource Manager Session (which must be created) exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA (); -- create a resource Plan, named Export sexecute DBMS_RESOURCE_MANAGER.CREATE_PLAN-(Plan => 'developers ', -Comment => 'developers, in Development database ');

5. Create a user group

Create two user groups: ONLINE_DEVELOPERS and BATCH_DEVELOPERS

-- Create a user group online_javassexec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP-(Consumer_Group => 'Online _ developers',-Comment => 'Online developers '); -- create a user group BATCH_DEVELOPERSexec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP-(Consumer_Group => 'batch _ developers',-Comment => 'batch developers ');

6. Understand resource allocation methods

Create two plan guides to allocate 75% of CPU resources to the ONLINE_DEVELOPERS group, and 25% of CPU resources to the BATCH_DEVELOPERS group.
In addition, the ONLINE_DEVELOPERS group concurrency is limited to 12, and the BATCH_DEVELOPERS group concurrency is limited to 6.

-- Online_appssexec example-(Plan => 'developers',-Group_or_subplan => 'Online _ developers',-Comment => 'Online developers',-Cpu_p1 => 75, -Cpu_p2 => 0,-Parallel_degree_limit_p1 => 12); -- batch_1_sexec limit-(Plan => 'developers',-Group_or_subplan => 'batch _ developers ', -Comment => 'batch developers',-Cpu_p1 => 25,-Cpu_p2 => 0,-Parallel_degree_limit_p1 => 6); -- OTHER_GROUPS (This group must be included in the plan) exec dbms_resource_manager.create_plan_directive-(Plan => 'developers',-Group_or_subplan => 'other _ GROUPS ',-Comment => 'everyone else',-Cpu_p1 => 0, -Cpu_p2 => 100,-Parallel_degree_limit_p1 => 6); -- verify whether the exec alias () is valid; -- submit the change exec alias (); -- clear the change exec DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA ();

7. assign a user group

Assign users to a user group reasonably,
Allocate JINGYU to Online_developers user group:

-- Grant the user group exec privileges-(grantee_name => 'jingyu ',-consumer_group => 'Online _ developers',-grant_option => true) to switch jingyu to the ONLINE_DEVELOPERS user group ); -- allocate jingyu to the Online_developers user group exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP-('jingyu ', 'Online _ developers ');

Assign ALFRED to the Batch_developers user group:

-- Grant the user group exec privileges to switch ALFRED to the BATCH_DEVELOPERS User Group-(grantee_name => 'alfred ',-consumer_group => 'batch _ developers',-grant_option => true ); -- Grant the administer_resource_manager permission to begin Queue (grantee_name => 'alfred ', privilege_name => 'administer _ resource_manager', admin_option => true); end; /-- allocate alfred to the Batch_developers user group exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP-('alfred ', 'batch _ developers ');

8. Activate the resource plan

-- Activate the resource scheduler Salter system set resource_manager_plan = 'developers ';

9. Understand the Resource Manager View

DBA_RSRC_PLANS-> resource plan and status dba_rsrc_plan_ctives VES-> resource plan -DBA_RSRC_CONSUMER_GROUPS-> resource plan user group member-> User Group user and role assignment DBA_RSRC_GROUP_MAPPINGS-> session attributes and user group user Group ing between DBA_RSRC_MAPPING_PRIORITY-> resource ing priority DBA_USERS-> INITIAL_RSRC_CONSUMER_GROUP column contains the user's initial User Role-> the user authorized on the DBMS_RESOURCE_MANAGER package

View the status and features of each plan:

Set linesize 120col plan for a30col status for a30select plan, status, num_plan_direves ves, mandatory from dba_rsrc_plans; The status column value is PENDING, indicating that the plan has not been verified and submitted successfully; if the value of the mandatory column is YES, the plan cannot be deleted.

10. Monitor Resource Manager

The following three dynamic performance views show Resource Manager statistics:

V $ rsrc_consumer_group-> CPU usage by user group v $ sysstat-> CPU usage by all sessions v $ sesstat-> CPU usage by session

You can also query the CPU, I/O, wait time, and queue session details using the statistics connection in the Resource Manager of EM.