After migrating your Oracle databases to Amazon Relational Database Service (Amazon RDS) for Oracle, you might want to exercise more control over CPU and other resources. For such use cases you can use Oracle Resource Database Manager to prioritize the CPU allocation for certain applications or users in order to have consistent performance for highly critical applications. This feature was introduced In Oracle 8i and is available in all subsequent versions of Oracle Enterprise Edition. The Oracle Resource Database Manager assigns a priority to database sessions, thereby ensuring that the most important transactions get the major share of system resources. It uses the attribute value defined during its configuration to prioritize the database resources utilization across the defined consumer groups (applications, users, and so on). Use your SQL*Plus client to connect to your Oracle database hosted on the on-premises server and run the following query to select the existing services that are set up on the database:
The following are some of the common use cases of using Resource Manager:
Distribute available CPU by allocating percentages of CPU time to different users and applications
Limit the degree of parallelism of any operation performed by members of a group of users
Limit the maximum CPU utilization for different users and applications
Quarantine the runaway queries
In the absence of a proper control mechanism and resource planning, there may be situations where critical database background processes may get starved for CPU. This can occur in the event foreground processes cause excessive load on the system due to thrashing. Another potential issue could be some jobs or applications competing for resources with each other, thus causing performance issues.
In this post, we show how to implement Oracle Database Resource Manager in an RDS for Oracle database using the CPU utilization attribute. These criteria can be used to distribute and limit the resources across high-priority web-based OLTP transactions and low-priority batch processing.
Solution overview
The following diagram shows an example of a Resource Manager configuration in the database where CPU allocation is done for multiple consumer groups using specific directives. You can configure Resource Manager with different settings and options. Refer to Managing Resources with Oracle Database Resource Manager for more details. You can use the Oracle Enterprise Manager (OEM) CPU distribution report based on multiple database services, or can query the Oracle data dictionary views to determine the values to use to configure these settings.
The following diagram illustrates the Resource Manager architecture.
Complete the following steps to configure Resource Manager on your RDS for Oracle database:
Create the database services.
Create a pending area and consumer groups.
Create the resource plan and plan directives.
Validate and submit the pending area.
Update the database parameter and application connect string.
Prerequisites
You need the following prerequisites:
Amazon RDS for Oracle database (Enterprise Edition)
SQL developer
A database user on the Amazon RDS for Oracle with required privileges:
System privilege: ADMINISTER_RESOURCE_MANAGER OR
Granted the privilege using DBMS_RESOURCE_MANAGER.GRANT_SYSTEM_PRIVILEGE by the Resource Manager administrator. For more details, refer to 27.1.3 About Resource Manager Administration Privileges
Create the database services
Amazon RDS for Oracle is a single-instance database. To configure Resource Manager based on your database services, create them in your RDS for Oracle database.
Use your SQL*Plus client to connect to your Oracle database hosted on the on-premises server and run the following query to select the existing services that are set up on the database:
Create the required database services on Amazon RDS for Oracle using the DBMS_SERVICE package. In the following example, we create two services for the purpose of illustration, but you can create as many services as you need.
Validate the new services created in the RDS for Oracle database:
After you create the database services using the DBMS_SERVICE.create_service procedure, you must start them manually:
Create a pending area and consumer groups
You need a pending area to work with Resource Manager and categorize sessions into multiple consumer groups. Database sessions are mapped to these consumer groups based on selected criteria such as database service name.
Create the pending area, which acts like a staging area where the resource plan can be defined and validated before it’s applied to the database:
Create the consumer groups to categorize the sessions:
Map the sessions to consumer groups based on the database service names used for connecting to the database. For the purpose of illustration, we use database services as the mapping criteria, but you can use other attributes for these mappings.
Create the resource plan and plan directive
A resource plan (plan) is a container for plan directives (directives) that specify how to allocate resources. Resource plan directives are used to allocate CPU resources to multiple consumer groups.
Complete the following steps:
Create a resource plan that will have the definition for high- and low-priority tasks via the resource plan directives:
Create the resource plan directives:
If a session is established with the database that doesn’t belong to any predefined consumer group, it’s considered to be a part of OTHER_GROUPS. Therefore, a plan directive must be added for OTHER_GROUPS as well.
Validate the plan directive was created. This is done to verify the CPU distribution percentage defined for different consumer groups.
If required you can manage runaway sessions or calls that use CPU, physical I/O, or logical I/O resources beyond a specified limit. When you create a resource plan directive for a consumer group, you can specify limits for CPU, physical I/O, or logical I/O resource consumption for sessions in that group. You can specify limits for physical I/O and logical I/O separately. You can also specify a limit for elapsed time.
Validate and submit the pending area
The changes done to the pending area created above needs to be validated and submitted for making it active.
Complete the following steps:
Validate the pending area to identify any errors:
Submit the pending area to apply the plan to the database:
Update the database parameter and application connect string
Resource plan created above needs to be activated at the database level. Also, the tnsnames.ora file on the client side need to be modified for the service names. This ensures that connections to the database will happen via server and resource manager can implement the directives.
Complete the following steps:
Update the following parameter in the initialization parameter file to activate Resource Manager upon database startup and set the top plan as ResourcePlan.
Restart the database and verify the parameter:
Start the database services manually after the database restart:
To connect the user or application session to the database, edit the tnsnames.ora or the connect string to use the service name created earlier in the application or the other clients:
Run the following SQL query to make sure the connections are established via database services and the created resource plan is active:
Points to note
The allocation of resources is done in terms of percentage. If allocation of resources for one consumer group is altered, it impacts other consumer groups as well.
There are a few known issues for Oracle database Resource Manager which are listed.
Clean up
To clean up your resources, complete the following steps:
Set resource_manager_plan as NULL:
Clear the pending area:
Create a pending area and delete the plan:
Delete the consumer groups:
Submit the pending area:
This cleanup operation switches Resource Manager off. It also resets the values stored in related data dictionary tables. You can delete the RDS instance if you are not using it. Please refer to note delete rds intance for details.
Conclusion
In this post, we demonstrated how you can use Oracle Database Resource Manager to distribute database resources across different services in an RDS for Oracle database. You can use this to prioritize resource distribution across high-priority and low-priority applications.
If you have any questions or comments about this post, use the comments section.
About the Authors
Abhishek Kumar Verma is a Senior Lead Consultant with the Amazon Web Services ProServe team. He has deep expertise in Database Migration and Administration on Amazon RDS for Oracle, Amazon RDS for PostgreSQL and Amazon Aurora PostgreSQL databases. He is also a subject matter expert in AWS DMS, Oracle Goldengate and Oracle Exadata. He works closely with customers to help migrate and modernize their databases and applications to AWS on a daily basis.
Puja Audhya is a Senior Lead Consultant with the Database Migration and Modernization team at Amazon Web Services. She helps AWS customers migrate the databases to AWS Cloud database services.
Read MoreAWS Database Blog
