Resource Management with Pluggable Database

There have been many Blogs around Pluggable Databases and very often the bloggers (and DBAs of course) are complaining that the individual pluggable databases are competing for resources since there is only one SGA, one set of Redo logs and one Undo-Tablespace.That’s true and should be taken into account whenever production databases are being consolidated into one container.

But for the CPU and degree of parallelism it is possible to limit the usage or prioritize some PDBs. In addition to the already existing Resource Plans (which can be used within any pluggable database as well) Oracle introduced CDB Resource plans which currently allow to throttle CPU consumption and number of parallel processes.

There are two ways for the definition:

• Using shares similar to VMware and other virtualization solutions
• Using hard limits for the resources

Let’s assume we do have 3 PDBS (JOHANN1, JOHANN2, and JOHANN1CLONE). Where JOHANN1 and JOHANN2 are my “important” databases and JOHANN1CLONE is just a test environment. It’s now very easy to create a plan_directive which gives JOHANN1 and JOHANN2 the same amount of shares and JOHANN1CLONE only 10 percent. In addition we don’t want any parallelism for the CLONE and the clone should only use a max of 50% of the CPU.

Create CDB Resource Plans:

To create or change resource plans it’s necessary to create a pending area first:

BEGIN
   dbms_resource_manager.create_pending_area();
END;

Now a CDB-Plan can be created. This one acts as the umbrella for all the individual plans and will later be used as the value for the parameter “resource_manager_plan”.

BEGIN
   dbms_resource_manager.create_cdb_plan(
      PLAN => 'johanns_plan',
      COMMENT => 'CDB Resource Plan for Database CJOHANN');
END;

As the next step the plan directives are created. This is where the individual PDBs are getting their resources:

BBEGIN
    dbms_resource_manager.create_cdb_plan_directive(
        plan                  => 'johanns_plan',
        pluggable_database    => 'JOHANN1',
        shares                => 100,
        utilization_limit     => 100,
        parallel_server_limit => 100);
    dbms_resource_manager.create_cdb_plan_directive(
        plan                  => 'johanns_plan',                 
        pluggable_database    => 'JOHANN2',
        shares                => 100,
        utilization_limit     => 100,
        parallel_server_limit => 100);
    dbms_resource_manager.create_cdb_plan_directive(
        plan                  => 'johanns_plan',
        pluggable_database    => 'JOHANN1CLONE',
        shares                => 10,
        utilization_limit     => 50,
        parallel_server_limit => 0);
END; 

As you can see, the syntax is very simple with only three variables:

• shares: number of shares for this PDB.
• utilization_limit: throttle for the CPU utilization (percentage of total). 100 indicates that this PDB can use all of the CPU if there is no competition.
• parallel_server_limit: throttle for the number of parallel server processes. Again 100 indicates that all parallel server processes can be used.

Now the pending area can be validated and if there is no error message the resource plan can be submitted.

BEGIN
    dbms_resource_manager.validate_pending_area();
END;

BEGIN
    dbms_resource_manager.submit_pending_area();
END;

We have now created a CDB resource plan. But this plan is not active.

To activate the CDB resource plan the parameter “RESOURCE_MANAGER_PLAN” must be set:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'johanns_plan';

The plan can be deactivated using the following command:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

To delete or change any plan items it’s again necessary to create a pending area. The following example will delete the entire CDB resource plan.

BEGIN
    dbms_resource_manager.create_pending_area();
    dbms_resource_manager.delete_cdb_plan(PLAN => 'johanns_plan');
    dbms_resource_manager.validate_pending_area();
    dbms_resource_manager.submit_pending_area();
END;

Unfortunately while testing I hit a bug because after resetting the parameter resource_manager_plan and deleting the CDB resource plan I accidentally tried to activate the plan again. This was what happened:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'johanns_plan';
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 21166
Session ID: 1 Serial number: 9

Finally the Instance crashed!

So be careful (but that’s obvious) when changing or activating plans.

Conclusion

This is obviously only the first step for a resource management.The most important one: buffer or I/O contention is not handled yet for ordinary databases. Oracle Exadata instead already has the ability to use resource management to throttle I/O. And I would assume that we will see some more mechanism in the next releases or patch set. Nevertheless it’s useful and very easy to implement and guess give’s us a better feeling when talking about resource competition between PDBs.