WHAT IS RESOURCE MANAGER
Resource Manager, is an Oracle database embedded resource allocation/scheduling mechanism that enables us to manage multiple different database workloads by assigning diferrent priorities to different consumer groups. It works just as the OS CPU scheduler except that, it works inside the database and aware of the consumer group priorities and the database-specific resources.
Resource manager allocates the database resources according to a resource plan and the resource groups mentioned in the plan. One example might be the resource plan called “DEFAULT_PLAN” where the “SYS_GROUP” is prioritized among the other groups “ORA$AUTOTASK” and “OTHER_GROUPS”. Therefore, in the case of an emergency, the SYS user can always find CPU resources to be able to fix a faulty plan. In a sense, the faulty plan is prohibited to consume excessive amounts of CPU in the presence of another high priority consumer group.
The resource manager is disabled by default.
TERMINOLOGY
- RESOURCE MANAGER: Oracle Database specific resource allocation/scheduling mechanism. By default it is disabled and the database treats all sessions alike. However, you can configure resource nanager to manage workloads differently by configuring consumer groups and resource plans.
- CONSUMER GROUP: is a collection of sessions that are managed as a unit. You can define consumer groups for each application in your database. Or you can define consumer groups for each type of workload, e.g. OLTP, reports, maintenance, etc.
- RESOURCE PLAN: is the directives stating how the CPU should be shared among the consumer groups.
- MAPPING RULES: Sessions can be automatically mapped to a consumer group by defining consumer group mapping rules. For example, a session from the “OLTP” service can be mapped to the “interactive” consumer group. Or a session with the username “sales” can be mapped to the “sales_app” consumer group. The attributes that can be used in consumer group mapping rules include the session’s service, module, action, Oracle username, client username, and program name.
ON RAC
On a RAC environment, Resource Manager manages each database instance independently. Each database instance can be configured with its own resource plan that reflects the applications it’s running. However, most deployments configure the same resource plan for all instances in the RAC database.
Configuring resource manager with a default plan:
Use the SYS user for below commands:
SQL> alter system set resource_manager_plan = 'DEFAULT_PLAN' sid = '*';
By enabling Resource Manager with the out-of-box “DEFAULT_PLAN”, you will get the following benefits:
- Critical background processes, such as PMON and LMS, won’t be starved for CPU, due to excessive load from foreground processes.
- SYSTEM and SYS are scheduled at the highest priority. Their response time will not be affected by runaway CPU activity from normal users, allowing them to always be able to login and debug database problems.
- Automated maintenance tasks are scheduled at the lowest priority: These tasks include gathering optimizer statistics and running the automatic segment and sql tuning advisors.
- These tasks will therefore not compete with other sessions for CPU. However, when the database workload is low, these maintenance tasks will be scheduled to consume any remaining, otherwise unused CPU resources.
Step-by-step guide for configuring resource manager for multiple different workloads (custom plan):
Use the SYS user for below commands:
1. Create a Pending Area :
SQL> exec dbms_resource_manager.create_pending_area();
2. Create Custom Consumer Groups :
SQL> exec dbms_resource_manager.create_consumer_group('SEARCH_GROUP', 'Sessions for the Search Sessions');
SQL> exec dbms_resource_manager.create_consumer_group('REPORT_GROUP', 'Sessions for the Reporting Sessions');
3. Configure the Mapping of Sessions to those new Consumer groups (the services should have already been created):
SQL> exec dbms_resource_manager.set_consumer_group_mapping(attribute => dbms_resource_manager.service_name, value => 'SEARCH', consumer_group => 'SEARCH_GROUP'); SQL> exec dbms_resource_manager.set_consumer_group_mapping(attribute => dbms_resource_manager.service_name, value => 'REPORT', consumer_group => 'REPORT_GROUP');
4. Create a Custom Resource Plan:
SQL> exec dbms_resource_manager.create_plan('CA_MIXED_PLAN', 'Plan for mixed workload including search, reporting and oltp');
5. Add Resource Plan Directives:
SQL> exec dbms_resource_manager.create_plan_directive('CA_MIXED_PLAN', 'SYS_GROUP', 'Percentage of CPU for SYS_GROUP', mgmt_p1 => 60);
SQL> exec dbms_resource_manager.create_plan_directive('CA_MIXED_PLAN', 'INTERACTIVE_GROUP', 'Percentage of CPU for INTERACTIVE_GROUP', mgmt_p1 => 20);
SQL> exec dbms_resource_manager.create_plan_directive('CA_MIXED_PLAN', 'OTHER_GROUPS', 'Percentage of CPU for OTHER_GROUPS', mgmt_p1 => 10);
SQL> exec dbms_resource_manager.create_plan_directive('CA_MIXED_PLAN', 'SEARCH_GROUP', 'Percentage of CPU for SEARCH_GROUP', mgmt_p1 => 5);
SQL> exec dbms_resource_manager.create_plan_directive('CA_MIXED_PLAN', 'REPORT_GROUP', 'Percentage of CPU for REPORT_GROUP', mgmt_p1 => 3);
SQL> exec dbms_resource_manager.create_plan_directive('CA_MIXED_PLAN', 'ORA$AUTOTASK', 'Percentage of CPU for ORA$AUTOTASK', mgmt_p1 => 2);
6. Submit the Pending Area:
SQL> exec dbms_resource_manager.submit_pending_area();
7. Enable the Resource Plan using the Custom Plan created:
SQL> alter system set resource_manager_plan = 'CA_MIXED_PLAN' sid='*';
8. View the current resource plan:
SQL> select name, cpu_managed from v$rsrc_plan where is_top_plan = 'TRUE';
You should see something like:
NAME CPU
--------------- ------
CA_MIXED_PLAN ON