Set up Database Resource Manager in Amazon RDS for Oracle

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:
```
select name from dba_services;
ORCL_A
SYS$BACKGROUND
SYS$USERS
```

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.

BEGIN
          DBMS_SERVICE.create_service(
           service_name => 'my_new_service1',
 network_name => 'my_new_service1'
);
END;
/
 
BEGIN
           DBMS_SERVICE.create_service(
           service_name => 'my_new_service2',
  network_name => 'my_new_service2'
);
END;
/

Validate the new services created in the RDS for Oracle database:

SELECT name,
       network_name
FROM   dba_services
ORDER BY 1;

ORCL_A ORCL_A
SYS$BACKGROUND
SYS$USERS
my_new_service my_new_service
my_new_service1 my_new_service1
my_new_service2 my_new_service2

After you create the database services using the DBMS_SERVICE.create_service procedure, you must start them manually:

BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'my_new_service2'
  );
END;
/
BEGIN
  DBMS_SERVICE.start_service(
    service_name => 'my_new_service1'
  );
END;
/

SELECT name,
       network_name
FROM   v$active_services
ORDER BY 1;

ORCL_A ORCL_A
SYS$BACKGROUND
SYS$USERS
my_new_service1 my_new_service1
my_new_service2 my_new_service2

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:
```
Execute DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
```

Create the consumer groups to categorize the sessions:

execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( 'Grpsev', 'Resource consumer group/method for connecting to mynewservice   service');
execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( 'Grpsev1', 'Resource consumer group/method for connecting to mynewservice1   service');
execute DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( 'Grpsev2', 'Resource consumer group/method for connecting to mynewservice2 service');

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.

BEGIN
   DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (
    attribute      => DBMS_RESOURCE_MANAGER.service_name,
    value          => 'my_new_service',
    consumer_group => 'Grpsev');
END;
/
 
BEGIN
   DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (
    attribute      => DBMS_RESOURCE_MANAGER.service_name,
    value          => 'my_new_service1',
    consumer_group => 'Grpsev1');
END;
/
 
BEGIN
  DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (
    attribute      => DBMS_RESOURCE_MANAGER.service_name,
    value          => 'my_new_service2',
    consumer_group => 'Grpsev2');
END;
/

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:
```
execute DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'ResourcePlan',   "COMMENT" => 'TOP level plan');
```

Create the resource plan directives:

execute DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'ResourcePlan',  GROUP_OR_SUBPLAN => 'Grpsev1',  "COMMENT" => 'Grp sev1   resource plan', MGMT_P1 => 80);
execute DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'ResourcePlan',  GROUP_OR_SUBPLAN => 'Grpsev2',  "COMMENT" => 'Grp sev2   resource plan', MGMT_P1 => 15);
execute DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'ResourcePlan',  GROUP_OR_SUBPLAN => 'OTHER_GROUPS',  "COMMENT" => 'Other Users', MGMT_P1 => 5);

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.

select plan, group_or_subplan, type, cpu_p1, cpu_p2, cpu_p3, cpu_p4, status   from dba_rsrc_plan_directives  where plan like 'RESOURCEPLAN'

RESOURCEPLAN GRPSEV1 CONSUMER_GROUP 80 0 0 0 PENDING

RESOURCEPLAN GRPSEV2 CONSUMER_GROUP 15 0 0 0 PENDING

RESOURCEPLAN OTHER_GROUPS CONSUMER_GROUP 5 0 0 0 PENDING

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:

Execute DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

Submit the pending area to apply the plan to the database:
```
Execute DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
```

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.
```
RESOURCE_MANAGER_PLAN = ResourcePlan
```

Restart the database and verify the parameter:

show spparameter resource_

SID NAME TYPE VALUE

--- ------------------------------- ------- ------------

* resource_limit boolean TRUE

* resource_manage_goldengate boolean

* resource_manager_cpu_allocation integer 1

* resource_manager_plan string ResourcePlan

Start the database services manually after the database restart:

BEGIN
   DBMS_SERVICE.start_service(
    service_name => 'my_new_service2'
  );
END;
/
BEGIN
   DBMS_SERVICE.start_service(
    service_name => 'my_new_service1'
  );

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:
```
service_name=myservice1
Connection = my_new_service1
host=<<Hostname>>
port= 1521
service_name=my_new_service2
Connection =my_new_service2
host=<<Hostname>>
port= 1521
```

Run the following SQL query to make sure the connections are established via database services and the created resource plan is active:

select sid , serial# , service_name  from v$session where service_name like '%my%' ;
642 13346 my_new_service1
1261 12930 my_new_service2

SELECT name, is_top_plan FROM v$rsrc_plan;
Name IsTopPlan
RESOURCEPLAN TRUE

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:

alter system set resource_manager_plan='';

Clear the pending area:

BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/

Create a pending area and delete the plan:

begin 
  dbms_resource_manager.create_pending_area(); 
end; 
/ 
begin 
 dbms_resource_manager.delete_plan( 
  plan => ‘ResourcePlan’); 
 end; 
/

Delete the consumer groups:

begin 
 dbms_resource_manager.delete_consumer_group( 
  consumer_group => 'Grpsev'); 
 dbms_resource_manager.delete_consumer_group( 
  consumer_group => 'Grpsev1'); 
 dbms_resource_manager.delete_consumer_group( 
  consumer_group => 'Grpsev2'); 
end; 
/

Submit the pending area:

begin 
 dbms_resource_manager.submit_pending_area(); 
end; 
/

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.

AWS Database Blog

Set up Database Resource Manager in Amazon RDS for Oracle

Solution overview

Prerequisites

Create the database services

Create a pending area and consumer groups

Create the resource plan and plan directive

Validate and submit the pending area

Update the database parameter and application connect string

Points to note

Clean up

Conclusion

About the Authors

Resources

Blog Topics

Follow