12c Multi-tenant Environment and DBRM for Container Database

In multi-tenant environment, Database Resource Manager (DBRM), at the Container Database (CDB) level enables us to manage the resources like CPU usage and number of parallel execution servers among the plugabble databases (PDBs). Then within each PDB, DBRM enables us to manage resources like CPU, parallelism and managing the runaway queries which exceed the certain thresholds of CPU, physical IO, logical IO or elapsed time.

In 12c version of Oracle database, at the CDB level, a CDB plan is implemented. This CDB plan allocates resources to the PDBs. A CDB plan is made up of directives, with each directive aimed at a single PDB. The directive controls the allocation of CPU and Parallel execution servers. The default CDB plan is DEFAULT_CDB_PLAN. CDB plan deals in share values and utilization limits.

Shares are like counters. More the share a PDB has, the more resources it would enjoy. The utilization limit for a PDB limits resource allocation to the PDB. By default, share value for each PDB is 1, and utilization limit is 100. Utilization limit restrains the system resource usage of a specific PDB. parallel_server_limit and PARALLEL_SERVERS_TARGET are used to limit parallel servers.

Example of a CDB Plan:

Following example enforces a CDB plan ‘cdb_plan’ for two databases ‘PROD’ and ‘DEV’. Prod has 2 shares and 100% utilization limit, whereas DEV has half of it’s shares i.e. 1 and 50% of utilization limit. Pending area is just a staging area to create, edit, and validate the plans.

exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
plan    => ‘cdb_plan’,
comment => ‘CDB plan for CDB’);
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan                  => ‘cdb_plan’,
pluggable_database    => ‘Prod’,
shares                => 2,
utilization_limit     => 100,
parallel_server_limit => 100);
END;
/

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
plan                  => ‘cdb_plan’,
pluggable_database    => ‘Dev’,
shares                => 1,
utilization_limit     => 50,
parallel_server_limit => 50);
END;
/

exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

You may use the DBA_CDB_RSRC_PLANS view to display all of the CDB resource plans defined in the CDB.