Oracle Resource Manager (ORM)
Goal of ORM:
To give Oracle Database Server more control over resource management decisions, thus preventing problems resulting from inefficient operating system management.
Problems addressed by ORM:• Excessive overhead
• Inefficient scheduling
• Inappropriate allocation of resources
• Inability to manage database-specific resources, such as parallel execution servers and active sessions
How above problems can be addressed:
• Guarantee specific users with certain amount of processing resources irrespective of the load on the system and the no. of users
Ex: HR_BATCH_GROUP – 50% CPU will be allocated for Batch processing jobs irrespective of the load on the system
• Distribute available processing resources by allocating percentages of CPU time to different users and applications. In a data warehouse, a higher percentage may be given to ROLAP (relational on-line analytical processing) applications than to batch jobs.
Ex: CPU Resources distribution for HR:
HR_BATCH_GROUP – 50% CPU
HR_WF_REPT_AUD_METADATA_GROUP – 20% CPU
HR_ADHOC_HRLINK_WF_GROUP – 15% CPU
HR_ADHOC_BUS_USER_GROUP – 5% CPU
HR_STEAM_DBLINK_USERS_GROUP – 5% CPU
OTHER_GROUPS – 5% CPU
• Limit to 10 concurrent sessions for HR_ADHOC_BUS_USER_GROUP so that members of the group performing any operation are limited to maximum of 10 sessions within the group. Additional sessions beyond the maximum are queued for execution, but you can specify a timeout period, after which queued jobs will terminate. Prevent the execution of operations that the optimizer estimates will run for a longer time than a specified limit
Ex: The parameter setting for this is ACTIVE_SESS_POOL_P1 => 10
Ex: The parameter setting for this is QUEUEING_P1 => 60
Ex: The parameter setting for this is MAX_EST_EXEC_TIME => 2700 (45 minutes)
• (not doing in below example) Allow automatic switching of users from one group to another group based on administrator defined criteria. If a member of a particular group of users creates a session that executes for longer than a specified amount of time, that session can be automatically switched to another group of users with different resource requirements.
• (not doing in below example) Create an undo pool. This pool consists of the amount of undo space that can be consumed in by a group of users.
• (not doing in below example) Limit the amount of time that a session can be idle. This can be further defined to mean only sessions that are blocking other sessions.
• (not doing in below example) Configure an instance to use a particular method of allocating resources. You can dynamically change the method, for example, from a daytime setup to a nighttime setup, without having to shut down and restart the instance.
• (not doing in below example) Allow the cancellation of long-running SQL statements and the termination of long-running sessions.
Terminology:
Resource Plan: A resource plan is a way to group a set of resource consumer groups together and specify how resources should be divided among them
Resource consumer groups: Allow the administrator to group user sessions together by resource requirements
Resource allocation methods: determine what policy to use when allocating for any particular resource. Resource allocation methods are used by resource plans and resource consumer groups
Resource plan directives: are a means of assigning consumer groups to particular plans and partitioning resources among consumer groups by specifying parameters for each resource allocation method
Subplans: allow further subdivision of resources among different users of an application
Levels: provide a mechanism to specify distribution of unused resources among available users. Up to eight levels of resource allocation can be specified
Steps for implementing ORM:
1. Execute the HR_PLAN.sql and check for any errors. HR_PLAN.sql is an example of how an ORM Plan can be created.
2. Do the following once ORM has been installed succesfully without any errors
$ sqlplus "/ as sysdba"
a. SQL> 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='HR_PLAN';
b. Set the init parameter RESOURCE_MANAGER_PLAN = HR_PLAN in database init parameter file"
Note: Both the above steps are required to make sure the created plan is active both dynamically and permanent
to ensure that if database is bounced the plan is still active in case database cannot be bounced now
HR_PLAN:
BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA;
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'HR_PLAN', COMMENT => 'HR ORM Plan');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_BATCH_GROUP', COMMENT => 'HR Batch group');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_WF_REPT_AUD_METADATA_GROUP', COMMENT => 'Reporting group');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_ADHOC_HRLINK_WF_GROUP', COMMENT => 'Ad-hoc WF HRLink group');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_ADHOC_BUS_USER_GROUP', COMMENT => 'Ad-hoc Business User group');
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'HR_STEAM_DBLINK_USERS_GROUP', COMMENT => 'Steam DBLink User group');
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_BATCH_GROUP', COMMENT => 'Batch Process', CPU_P1 => 50);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_WF_REPT_AUD_METADATA_GROUP', COMMENT => 'Reporting Process', CPU_P1 =
> 20);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_ADHOC_HRLINK_WF_GROUP', COMMENT => 'Ad-hoc WF HRLink group', CPU_P1 =
> 15);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_ADHOC_BUS_USER_GROUP', COMMENT => 'Ad-hoc Business User group', CPU_P
1 => 5, MAX_EST_EXEC_TIME => 2700 , ACTIVE_SESS_POOL_P1 => 10, QUEUEING_P1 => 60);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN', GROUP_OR_SUBPLAN => 'HR_STEAM_DBLINK_USERS_GROUP', COMMENT => 'Steam User group', CPU_P1 => 5
);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'HR_PLAN',GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'mandatory', CPU_P1 => 5);
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SYSADM', 'HR_BATCH_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('SYSADM', 'HR_BATCH_GROUP');
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('WFLINK', 'HR_ADHOC_HRLINK_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('WFLINK', 'HR_ADHOC_HRLINK_WF_GROUP');
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('BUS_AH32366', 'HR_ADHOC_BUS_USER_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('BUS_AH32366', 'HR_ADHOC_BUS_USER_GROUP');
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('HR_LINK_APAC','HR_ST_DBLINK_USERS_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('HR_LINK_APAC','HR_STEAM_DBLINK_USERS_GROUP');
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('CSBOUTA1','HR_REPT_AUD_METADATA_GROUP', TRUE);
DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('CSBOUTA1','HR_WF_REPT_AUD_METADATA_GROUP');
END;
/
Steps for enabling ORM:
There are two ways for enabling ORM after you create your custom ORM Plan in the database. Below are the steps:
1. Enable it dynamically from a SQLPLUS Session
Ex: SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=’HR_PLAN’;
2. Enable it permanently in init.ora if you are using pfile
Ex: In init file add the parameter – RESOURCE_MANAGER_PLAN=’HR_PLAN’;
Both the above steps are recommended as database needs to be bounced if you update init file and if that cannot be done immediately Step 1 will take care of enabling the Resource Manager immediately and even if the database is bounced Step 2 will take care of enabling it Resource Manager automatically.
Steps for disabling ORM:There are two ways for enabling ORM after you create your custom ORM Plan in the database. Below are the steps:
1. Disable it dynamically from a SQLPLUS Session
Ex: SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=’ ’; (space in between)
2. Disable it permanently in init.ora if you are using pfile
Ex: In init file comment out or remove the parameter – RESOURCE_MANAGER_PLAN=’HR_PLAN’;
ORM FAQ's:
Q: How does the Resource Manager work in a multi-instance server?
A: The resource manager cannot manage resources across instances, only within an instance. It would not be useful in multi-instance on a single server situation. If you are having more than one instance, you must use OS tools such as taking a big honking Sun E10k and setting up separate domains (so all of a sudden you don't have more than one instance per "server")
Q: Overhead of using "Oracle Resource Manager"?
A: The implementation of a resource plan is going to take some resources itself (of course). You do not quantify at all what "non-insignificant" is - so I will not address that (everyone has a different definition for that...). so it is not really possible to comment.
Note: The above question has been taken from www.asktom.oracle.com website
Q: When does "Oracle Resource Manager" (ORM) kick in?
A: Resource manager basically does nothing until a resource is totally maxed out (you cannot put CPU in the bank and use it later). So, even if you say "100% to group A", unless group A actually needs to consume 100%. Resource manager is not a "fence", but rather an attempt to assure you that if group A makes a demand for X%, it will be given to them.
Q: Will there be any degradation in response time or execution time of jobs than the regular taken time?
A: Yes, when the server is busy and is at 100% CPU or more the jobs will take a long time to finish than what they might be taking regularly. But the response time will almost be the same.
Q. What is the overhead after implementing ORM?
A: There was no specific answer for what is the overhead of using ORM resource regulation. So, I did a test to find out the results of which are presented here but there is minimal to no impact while the server is not 100% busy and there might very little overhead when the server is 100% which hasn't been quantified yet but I did some estimation based on some tests.
Reference: Based on information read from Oracle Documentation and other online websites
No comments:
Post a Comment