Apr 08 2020

Configure Resource Manager on Container Database

The following issues DBAs may face in oracle databases but we can overcome these issues by using Resource Manager:

  • Excessive overhead: Excessive overhead results from operating system context switching between Oracle Database server processes when the number of server processes is high.
  • Inefficient scheduling: The operating system deschedules database servers while they hold latches, which is inefficient.
  • Inappropriate allocation of resources: The operating system distributes resources equally among all active processes and cannot prioritize one task over another.
  • Inability to manage database-specific resources, such as parallel execution servers and active sessions

Resource Plan: In CDB or Non-CDB Database, Resource plan controls allocation of CPU and parallel execution servers. In a PDB, it is used to specify how the resources are to be distributed among the consumer groups in that specific PDB. Several resource plans might be created, but only one will be active at a time.

Consumer Group: It is a set of sessions that are grouped based on resource requirements. When a session is created in the database, it is automatically mapped to a consumer group. There are two default consumer groups in the database: SYS_GROUP and OTHER_GROUPS.

Create CDB Resource Plan on Container Database using OEM:

Select the Container Database from the list on which you want to configure the Resource Plan.

Select Container Database.

On Administration Menu, Select “Resource Manager” from Drop Down list.

Click on “CDB Resource Plans” Like under Container Database session.

Click on Create Button on right top corner to create a new Resource Plan.

Enter Plan Name with Description (optional). Click on Add/Remove button to assign Shares parameter values (=percentage of CUP) to all/selected PDBs.

Move PDB from “Available PDBs” list to “Selected PDBs” list. Click OK.

Now you have an option to assign “Shares”, “Utilization Limit %” and “Parallel Server Limit %” values for each PDBs.

Enter values on “Shares”, “Utilization Limit %” and “Parallel Server Limit %” based on priority of PDBs, Click Show SQL and return then it will reflect the Percentage value automatically.

Click Apply. It will show the confirmation message like below.

Select the Resource Plan and Click on Edit.

Now you can change values for existing Resource Plan as well as enable the Plan by Selecting check box for “Active this plan“.

Plan is activated.

Create CDB Resource Plan Using PL/SQL:

For Example, we have 3 PDBs and assigned below mentioned shares values:

  • Shares Values for:
    • PDBNODATA: 1
    • PDBTEST: 4
    • SALESPDB: 3

Resource Allocation Calculation (from Shares to %): (assigned values for PDB * 100) / summation of shares values (1+4+3)

Resource allocation with Percentage: If use Shares Parameter then don’t need to use the percentage. So either have to use Shares or Percentage.

  • PDBNODATA: (1*100)/8= 12%
  • PDBTEST : (4*100)/8 = 50%
  • SALESPDB: (3*100/8) = 37%
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='RM_TEST';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_cdb_plan( plan => 'RM_TEST', comment => 'RM');
dbms_resource_manager.create_cdb_plan_directive(
    plan => 'RM_TEST',
    pluggable_database => 'PDBNODATA',
    comment => '',
    shares => 1,
    utilization_limit => NULL,
   parallel_server_limit => NULL );
dbms_resource_manager.create_cdb_plan_directive(
    plan => 'RM_TEST',
    pluggable_database => 'PDBTEST',
    comment => '',
    shares => 4,
    utilization_limit => NULL,
   parallel_server_limit => NULL );
dbms_resource_manager.create_cdb_plan_directive(
    plan => 'RM_TEST',
    pluggable_database => 'SALESPDB',
    comment => '',
    shares => 3,
    utilization_limit => NULL,
   parallel_server_limit => NULL );
dbms_resource_manager.submit_pending_area();
END;
/

Update Existing CDB Resource Plan using PL/SQL:

We can change values for -> Shares or Utilization Limit or Parallel Server Limit parameters. 

DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='RM_TEST';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_cdb_plan_directive(
    plan => 'RM_TEST',
    pluggable_database => 'PDBNODATA',
    new_comment => '',
    new_shares => 1,
    new_utilization_limit => 50,
   new_parallel_server_limit => 50 );
dbms_resource_manager.update_cdb_plan_directive(
    plan => 'RM_TEST',
    pluggable_database => 'PDBTEST',
    new_comment => '',
    new_shares => 4,
    new_utilization_limit => 90,
   new_parallel_server_limit => 90 );
dbms_resource_manager.update_cdb_plan_directive(
    plan => 'RM_TEST',
    pluggable_database => 'SALESPDB',
    new_comment => '',
    new_shares => 3,
    new_utilization_limit => 70,
   new_parallel_server_limit => 70 );
dbms_resource_manager.submit_pending_area();
END;
/

Note::: If create a new PDB on Container Database then have to add the new PDB to the Resource Plan manually.

Create a Resource Plan on PDB:

Create a Consumer Groups on PDB:

More Details about Resource Manager: Resource Manager