Blog | Jan 14, 2014

Manage CPU Caging with Multitenant Database in version 12c!

Are you interested in roping in more CPU for one of your important database running along multiple databases? If yes, than this blog is for you. While hosting multiple databases, some databases are more critical than others. Sometimes multiple non-prod databases are hosted on a single server and a particular database can become critical (due to Application Testing) from CPU allocation point of view. Obviously the box won’t know the criticality and hence can’t prioritize the processes for your chosen database.

Occasionally, RMAN (Recovery Manager) Backup or Stats Gathering Job utilizes the whole CPU resource and other parallel running Batch Jobs have a substantial wait for the CPU or get small percentage of CPU Time. By using instance caging, a DBA may control how the CPU is allocated to the most important database and also control the usage of CPU resource optimally.

In version 11g, Resource Plan can be associated with single database only i.e. Resource Plan will manage its own database. However, with the introduction of the Multitenant Databases concept in version 12c, a Resource Plan can be associated with the Multiple Pluggable Databases and each Pluggable database can have its own Resource Plan along with the Master Resource Plan. For example, in version 12c if Pluggable Database is allowed 40% CPU by Master Resource Plan, now 40% will be ceiling for any Resource Plan created by that Pluggable Database.

Enhancements in Oracle Database 12c

CPU Caging has undergone changes since the introduction of Oracle Database 12c. Let’s discuss those enhancements. As stated above Oracle Database 12c comes with Oracle Multitenant option to configure and manage a multitenant environment. This new architecture enables an Oracle database to function as a multitenant container database (CDB) that includes zero to many customers-created pluggable databases (PDBs).
As we all know, by default, Operating System allocates CPU resources equally among all running databases and the same stands correct for all PDBs running under the CDB.

In a non-CDB, you can use the Resource Manager to manage multiple workloads that are contending for system and database resources. However, in a CDB, you can have multiple workloads within multiple PDBs competing for system and CDB resources. For Instance, a TEST database can have multiple PDB for each application and we want to prioritize CPU resource based on application criticality. Resource Manager in 12c can define custom resource allocation for each PDB.

In a CDB, Resource Manager can manage resources on two basic levels:
CDB level defines how resources are allocated to child PDBs, and you can limit the resource utilization of a specific PDB.
PDB level defines management of workloads within each PDB.

Resource Manager in Oracle Database 12c is enhanced in the following techniques -

• We can specify how different PDBs should receive their share of CPU resources helping allocate more resources to the more important PDBs.
• Parallel Execution Servers of a particular PDB can be restricted.
• CPU usage of a particular PDB can be limited to a custom extent.
• Resource usage of each PDB can be monitored separately.

PDB resource plan are created in the same way as they are created in a non-CDB. DBMS_RESOURCE_MANAGER PL/SQL package is used to create plans. Before proceeding make sure you are connected to a right PDB.

Task 1   Create a Pending Area

Create a pending area using the CREATE_PENDING_AREA procedure:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

Task 2   Create the CDB Resource Plan

Create a CDB resource plan named testcdb_plan using the CREATE_CDB_PLAN procedure:
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
    plan  => 'testcdb_plan',    comment => 'CDB resource plan for testcdb');
END;
/

Task 3   Create Directives for the PDBs

Create the CDB resource plan directives for the PDBs using the CREATE_CDB_PLAN_DIRECTIVE procedure. Each directive specifies how resources are allocated to a specific PDB.
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'testcdb_plan',
    pluggable_database    => 'testpdb1',
    shares                => 3,
    utilization_limit     => 100,
    parallel_server_limit => 100);
END;
/
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'testcdb_plan',
    pluggable_database    => ' testpdb2',
    shares                => 3,
    utilization_limit     => 100,
    parallel_server_limit => 100);
END;
/
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'testcdb_plan',
    pluggable_database    => 'testpdb3',
    shares                => 1,
    utilization_limit     => 70,
    parallel_server_limit => 70);
END;
/
Task 4   Validate the Pending Area

Validate the pending area using the VALIDATE_PENDING_AREA procedure:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

Task 5   Submit the Pending Area

Submit the pending area using the SUBMIT_PENDING_AREA procedure:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

Check  CDB Resource Plans

select plan, status, comments from dba_cdb_rsrc_plans order by plan;

Check CDB Database Resource Plan Directives

COLUMN PLAN HEADING 'Plan' FORMAT A26
COLUMN PLUGGABLE_DATABASE HEADING 'Pluggable|Database' FORMAT A25
COLUMN SHARES HEADING 'Shares' FORMAT 999
COLUMN UTILIZATION_LIMIT HEADING 'Utilization|Limit' FORMAT 999
COLUMN PARALLEL_SERVER_LIMIT HEADING 'Parallel|Server|Limit' FORMAT 999
select plan, pluggable_database, shares, utilization_limit, parallel_server_limit from dba_cdb_rsrc_plan_directives order by plan;

It can be concluded that by using Resource Plans we can manage CPU Resource optimally and can give preference to more critical databases residing on the same server.

For more details on using Resource Plans with Container Databases follow the links below -
How to access CDB and PDB
http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_admin.htm#BAJCEFJD

Using Resource Plan for CDB in Oracle 12c
http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_dbrm.htm