44 Using Oracle Resource Manager for PDBs with SQL*Plus

What Solutions Does Resource Manager Provide for a CDB?

When resource allocation decisions for a CDB are left to the operating system, you may encounter the following problems with workload management:

• Inappropriate allocation of resources among PDBs

  The operating system distributes resources equally among all active processes and cannot prioritize one task over another. Therefore, one or more PDBs might use an inordinate amount of the system resources, leaving the other PDBs starved for resources.

• Inappropriate allocation of resources within a single PDB

  One or more sessions connected to a single PDB might use an inordinate amount of the system resources, leaving other sessions connected to the same PDB starved for resources.

• Inconsistent performance of PDBs

  A single PDB might perform inconsistently when other PDBs are competing for more system resources or less system resources at various times.

• Lack of resource usage data for PDBs

  Resource usage data is critical for monitoring and tuning PDBs. It might be possible to use operating system monitoring tools to gather the resource usage data for a non-CDB if it is the only database running on the system. However, in a CDB, operating system monitoring tools are no longer as useful because there are multiple PDBs running on the system.

Resource Manager helps to overcome these problems by allowing the CDB more control over how hardware resources are allocated among the PDBs and within PDBs.

In a CDB with multiple PDBs, some PDBs typically are more important than others. The Resource Manager enables you to prioritize and limit the resource usage of specific PDBs.

With the Resource Manager, you can:

• Specify that different PDBs should receive different shares of the system resources so that more resources are allocated to the more important PDBs

• Limit the CPU usage of a particular PDB

• Limit the number of parallel execution servers that a particular PDB can use

• Limit the resource usage of different sessions connected to a single PDB

• Monitor the resource usage of PDBs

CDB Resource Plans

In a CDB, PDBs might have different levels of priority. You can create CDB resource plans to distribute resources to different PDBs based on these priorities.

This section contains the following topics:

• About CDB Resource Plans

• Shares for Allocating Resources to PDBs

• Utilization Limits for PDBs

• The Default Directive for PDBs

Shares for Allocating Resources to PDBs

To allocate resources among PDBs, you assign a share value to each PDB. A higher share value results in more guaranteed resources for a PDB.

You specify a share value for a PDB using the CREATE_CDB_PLAN_DIRECTIVE procedure in the DBMS_RESOURCE_MANAGER package. The shares parameter in this procedure specifies the share value for the PDB.

Figure 44-1 shows an example of three PDBs with share values specified for them in a CDB resource plan.

Figure 44-1 Shares in a CDB Resource Plan

Description of "Figure 44-1 Shares in a CDB Resource Plan"

Figure 44-1 shows that the total number of shares is seven (3+3+1). The salespdb and the servicespdb PDB are each guaranteed 3/7th of the resources, while the hrpdb PDB is guaranteed 1/7th of the resources. However, any PDB can use more than the guaranteed amount of a resource if there is no resource contention.

Table 44-1 shows the resources allocation to the PDBs in Figure 44-1 based on the share values, assuming that loads of the PDBs consume all of the system resources allocated.

Table 44-1 Resource Allocation for Sample PDBs

Resource	Resource Allocation
CPU	The salespdb and servicespdb PDBs can consume the same amount of CPU resources. The salespdb and servicespdb PDBs are each guaranteed three times more CPU resource than the hrpdb PDB. See "CPU" for more information about this resource.
Parallel execution servers	Queued parallel queries from the salespdb and servicespdb PDBs are selected equally. Queued parallel queries from the salespdb and servicespdb PDBs are selected three times as often as queued parallel queries from the hrpdb PDB. See "Degree of Parallelism Limit" for more information about this resource.

Utilization Limits for PDBs

A utilization limit restrains the system resource usage of a specific PDB. You can specify utilization limits for CPU and parallel execution servers.

Table 44-2 describes utilization limits for PDBs and the Resource Manager action taken when a PDB reaches a utilization limit.

Table 44-2 Utilization Limits for PDBs

Resource	Resource Utilization Limit	Resource Manager Action
CPU	The sessions connected to a PDB reach the CPU utilization limit for the PDB. This utilization limit for CPU is set by the utilization_limit parameter in the CREATE_CDB_PLAN_DIRECTIVE procedure of the DBMS_RESOURCE_MANAGER package. The utilization_limit parameter specifies the percentage of the system resources that a PDB can use. The value ranges from 0 to 100.	Resource Manager throttles the PDB sessions so that the CPU utilization for the PDB does not exceed the utilization limit.
Parallel execution servers	A PDB uses more than the value of the PARALLEL_SERVERS_TARGET initialization parameter multiplied by the value of the parallel_server_limit parameter in the CREATE_CDB_PLAN_DIRECTIVE procedure. For example, if the PARALLEL_SERVERS_TARGET initialization parameter is set to 200 and the parallel_server_limit parameter for a PDB is set to 10%, then utilization limit for the PDB is 20 parallel execution servers (200 X .10).	Resource Manager queues parallel queries if the number of parallel execution servers used by the PDB would exceed the limit specified by the PARALLEL_SERVERS_TARGET initialization parameter value multiplied by the value of the parallel_server_limit parameter in the CREATE_CDB_PLAN_DIRECTIVE procedure.

Figure 44-2 shows an example of three PDBs with shares and utilization limits specified for them in a CDB resource plan.

Figure 44-2 Shares and Utilization Limits in a CDB Resource Plan

Description of "Figure 44-2 Shares and Utilization Limits in a CDB Resource Plan"

Figure 44-2 shows that there are no utilization limits on the salespdb and servicespdb PDBs because utilization_limit and parallel_server_limit are both set to 100% for them. However, the hrpdb PDB is limited to 70% of the applicable system resources because utilization_limit and parallel_server_limit are both set to 70%.

The Default Directive for PDBs

When you do not explicitly define directives for a PDB, the PDB uses the default directive for PDBs. Table 44-3 shows the attributes of the initial default directive for PDBs.

Table 44-3 Initial Default Directive Attributes for PDBs

Directive Attribute	Value
shares	1
utilization_limit	100
parallel_server_limit	100

When a PDB is plugged into a CDB and no directive is defined for it, the PDB uses the default directive for PDBs.

You can create new directives for the new PDB. You can also change the default directive attribute values for PDBs by using the UPDATE_CDB_DEFAULT_DIRECTIVE procedure in the DBMS_RESOURCE_MANAGER package.

When a PDB is unplugged from a CDB, the directive for the PDB is retained. If the same PDB is plugged back into the CDB, then it uses the directive defined for it if the directive was not deleted manually.

Figure 44-3 shows an example of the default directive in a CDB resource plan.

Figure 44-3 Default Directive in a CDB Resource Plan

Description of "Figure 44-3 Default Directive in a CDB Resource Plan"

Figure 44-3 shows that the default PDB directive specifies that the share is 1, the utilization_limit is 50%, and the parallel_server_limit is 50%. Any PDB that is part of the CDB and does not have directives defined for it uses the default PDB directive. Figure 44-3 shows the PDBs marketingpdb and testingpdb using the default PDB directive. Therefore, marketingpdb and testingpdb each get 1 share and a utilization limit of 50.

See Also:

• "Creating New CDB Resource Plan Directives for a PDB"

• "Updating the Default Directive for PDBs in a CDB Resource Plan"

• Chapter 38, "Creating and Removing PDBs with SQL*Plus"

• "Unplugging a PDB from a CDB"

• "Parallel Server Limit"

PDB Resource Plans

A CDB resource plan determines the amount of resources allocated to each PDB. A PDB resource plan determines how the resources allocated to a specific PDB are allocated to consumer groups within that PDB. A PDB resource plan is similar to a resource plan for a non-CDB. Specifically, a PDB resource plan allocates resource among the consumer groups within a PDB. You can use a PDB resource plan to allocate the resources described in "The Types of Resources Managed by the Resource Manager".

When you create one or more PDB resource plans, the CDB resource plan for the PDB's CDB should meet certain requirements. Table 44-4 describes the requirements for the CDB resource plan and the results when the requirements are not met.

You create directives for a CDB resource plan by using the CREATE_CDB_PLAN_DIRECTIVE procedure in the DBMS_RESOURCE_MANAGER package. You create directives for a PDB resource plan using the CREATE_PLAN_DIRECTIVE procedure in the same package. When you create one or more PDB resource plans and there is no CDB resource plan, the CDB uses the DEFAULT_CDB_PLAN that is supplied with Oracle Database.

Figure 44-4 shows an example of a CDB resource plan and a PDB resource plan.

Figure 44-4 A CDB Resource Plan and a PDB Resource Plan

Description of "Figure 44-4 A CDB Resource Plan and a PDB Resource Plan"

Figure 44-4 shows some of the directives in a PDB resource plan for the servicespdb PDB. Other PDBs in the CDB can also have PDB resource plans.

In a CDB, the following restrictions apply to PDB resource plans:

• A PDB resource plan cannot have subplans.

• A PDB resource plan can have a maximum of eight consumer groups.

• A PDB resource plan cannot have a multiple-level scheduling policy.

If you create a PDB using a non-CDB, and the non-CDB contains resource plans, then these resource plans might not conform to these restrictions. In this case, Oracle Database automatically transforms these resource plans into equivalent PDB resource plans that meet these requirements. The original resource plans and directives are recorded in the DBA_RSRC_PLANS and DBA_RSRC_PLAN_DIRECTIVES views with the LEGACY status.

Background and Administrative Tasks and Consumer Groups

In a CDB, background and administrative tasks are mapped to the Resource Manager consumer groups that run them optimally. Resource Manager uses the following rules to map a task to a consumer group:

• A task is mapped to a consumer group in the container that starts the task. If a task starts in the root, then the task is mapped to a consumer group in the root. If the task starts in a PDB, then the task is mapped to a consumer group in the PDB.

• When a task is started by an internal client using an internal API, the internal API determines the consumer group to which the task is mapped.

  For example, a backup task uses an internal Oracle function. When SYS starts a backup task in the root, the backup task is mapped based on the Oracle function to the SYS_GROUP consumer group in the root.

• When a task is started without using an internal API, the task is mapped to a consumer group based on the user-defined mapping rules.

  For example, when SYS is mapped to the SYS_GROUP consumer group, a task started by SYS is mapped to the SYS_GROUP consumer group.

The following background and administrative tasks follow these rules:

• Backup and recovery

• Auditing

• Replication and Advanced Queuing

• Unplugging a PDB

• Maintenance windows

Creating a CDB Resource Plan: A Scenario

This section uses a scenario to illustrate each of the steps involved in creating a CDB resource plan. The scenario assumes that you want to create a CDB resource plan for a CDB named newcdb. The plan includes a directive for each PDB. In this scenario, you also update the default directive and the autotask directive.

The directives are defined using various procedures in the DBMS_RESOURCE_MANAGER package. The attributes of each directive are defined using parameters in these procedures. Table 44-5 describes the types of directives in the plan.

Table 44-6 describes how the CDB resource plan allocates resources to its PDBs using the directive attributes described in Table 44-5.

The salespdb and servicespdb PDBs are more important than the other PDBs in the CDB. Therefore, they get a higher share (3), unlimited CPU utilization resource, and unlimited parallel execution server resource.

The default directive applies to PDBs for which specific directives have not been defined. For this scenario, assume that the CDB has several PDBs that use the default directive. This scenario updates the default directive.

In addition, this scenario updates the autotask directive. The autotask directive applies to automatic maintenance tasks that are run in the root maintenance window.

The following tasks use the DBMS_RESOURCE_MANAGER package to create the CDB resource plan and update the default and autotask directives for this scenario:

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 newcdb_plan using the CREATE_CDB_PLAN procedure:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN(
    plan    => 'newcdb_plan',
    comment => 'CDB resource plan for newcdb');
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.

Table 44-6 describes the directives for the salespdb, servicespdb, and hrpdb PDBs in this scenario. Run the following procedures to create these directives:

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'newcdb_plan', 
    pluggable_database    => 'salespdb', 
    shares                => 3, 
    utilization_limit     => 100,
    parallel_server_limit => 100);
END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'newcdb_plan', 
    pluggable_database    => 'servicespdb', 
    shares                => 3, 
    utilization_limit     => 100,
    parallel_server_limit => 100);
END;
/

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
    plan                  => 'newcdb_plan', 
    pluggable_database    => 'hrpdb', 
    shares                => 1, 
    utilization_limit     => 70,
    parallel_server_limit => 70);
END;
/

All other PDBs in this CDB use the default PDB directive.

Task 4   (Optional) Update the Default PDB Directive

If the current default CDB resource plan directive for PDBs does not meet your requirements, then update the directive using the UPDATE_CDB_DEFAULT_DIRECTIVE procedure.

The default directive applies to PDBs for which specific directives have not been defined. See "The Default Directive for PDBs" for more information.

Table 44-6 describes the default directive that PDBs use in this scenario. Run the following procedure to update the default directive:

BEGIN
  DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE(
    plan                      => 'newcdb_plan', 
    new_shares                => 1, 
    new_utilization_limit     => 50,
    new_parallel_server_limit => 50);
END;
/

Task 5   (Optional) Update the Autotask Directive

If the current autotask CDB resource plan directive does not meet your requirements, then update the directive using the UPDATE_CDB_AUTOTASK_DIRECTIVE procedure.

The autotask directive applies to automatic maintenance tasks that are run in the root maintenance window.

Table 44-6 describes the autotask directive in this scenario. Run the following procedure to update the autotask directive:

BEGIN
  DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE(
    plan                      => 'newcdb_plan', 
    new_shares                => 1, 
    new_utilization_limit     => 75,
    new_parallel_server_limit => 75);
END;
/

Task 6   Validate the Pending Area

Validate the pending area using the VALIDATE_PENDING_AREA procedure:

exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();

Task 7   Submit the Pending Area

Submit the pending area using the SUBMIT_PENDING_AREA procedure:

exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

Enabling a CDB Resource Plan

You enable the Resource Manager for a CDB by setting the RESOURCE_MANAGER_PLAN initialization parameter in the root. This parameter specifies the top plan, which is the plan to be used for the current CDB instance. If no plan is specified with this parameter, then the Resource Manager is not enabled.

Before enabling a CDB resource plan, complete the prerequisites described in "Prerequisites for Using Resource Manager with a CDB".

To enable a CDB resource plan: 

1. In SQL*Plus, ensure that the current container is the root.

   See "Accessing a Container in a CDB with SQL*Plus".

2. Perform one of the following actions:

   • Use an ALTER SYSTEM statement to set the RESOURCE_MANAGER_PLAN initialization parameter to the CDB resource plan.

     The following example sets the CDB resource plan to newcdb_plan using an ALTER SYSTEM statement:

     ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'newcdb_plan';
     

   • In a text initialization parameter file, set the RESOURCE_MANAGER_PLAN initialization parameter to the CDB resource plan, and restart the CDB.

     The following example sets the CDB resource plan to newcdb_plan in an initialization parameter file:

     RESOURCE_MANAGER_PLAN = 'newcdb_plan'
     

You can also schedule a CDB resource plan change with Oracle Scheduler. See "Enabling Oracle Database Resource Manager and Switching Plans" and Chapter 28, "Oracle Scheduler Concepts" for more information.

Disabling a CDB Resource Plan

You disable the Resource Manager for a CDB by unsetting the RESOURCE_MANAGER_PLAN initialization parameter in the root. If you disable a CDB resource plan, then some directives in PDB resource plans become disabled. See "PDB Resource Plans" for information about the CDB resource plan requirements for PDB resource plans.

Before disabling a CDB resource plan, complete the prerequisites described in "Prerequisites for Using Resource Manager with a CDB".

To disable a CDB resource plan: 

1. In SQL*Plus, ensure that the current container is the root.

   See "Accessing a Container in a CDB with SQL*Plus".

2. Perform one of the following actions:

   • Use an ALTER SYSTEM statement to unset the RESOURCE_MANAGER_PLAN initialization parameter for the CDB.

     The following example unsets the RESOURCE_MANAGER_PLAN initialization parameter using an ALTER SYSTEM statement:

     ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
     

   • In an initialization parameter file, unset the RESOURCE_MANAGER_PLAN initialization parameter, and restart the CDB.

     The following example unsets the RESOURCE_MANAGER_PLAN initialization parameter in an initialization parameter file:

     RESOURCE_MANAGER_PLAN = 
     

     To shut down and restart a CDB, see "Shutting Down a CDB Instance" and "Starting Up a Database".

Enabling a PDB Resource Plan

You enable a PDB resource plan by setting the RESOURCE_MANAGER_PLAN initialization parameter to the plan with an ALTER SYSTEM statement when the current container is the PDB. If no plan is specified with this parameter, then no PDB resource plan is enabled for the PDB.

Before enabling a PDB resource plan, complete the prerequisites described in "Prerequisites for Using Resource Manager with a CDB".

To enable a PDB resource plan: 

1. In SQL*Plus, ensure that the current container is a PDB.

   See "Accessing a Container in a CDB with SQL*Plus".

2. Use an ALTER SYSTEM statement to set the RESOURCE_MANAGER_PLAN initialization parameter to the PDB resource plan.

You can also schedule a PDB resource plan change with Oracle Scheduler. See "Enabling Oracle Database Resource Manager and Switching Plans" and Chapter 28, "Oracle Scheduler Concepts" for more information.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'salespdb_plan';

Disabling a PDB Resource Plan

You disable a PDB resource plan by unsetting the RESOURCE_MANAGER_PLAN initialization parameter in the PDB.

Before disabling a PDB resource plan, complete the prerequisites described in "Prerequisites for Using Resource Manager with a CDB".

To disable a PDB resource plan: 

1. In SQL*Plus, ensure that the current container is a PDB.

   See "Accessing a Container in a CDB with SQL*Plus".

2. Use an ALTER SYSTEM statement to unset the RESOURCE_MANAGER_PLAN initialization parameter for the PDB.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

Managing a CDB Resource Plan

This section provides instructions for managing a CDB resource plan.

This section contains the following topics:

• Updating a CDB Resource Plan

• Creating New CDB Resource Plan Directives for a PDB

• Updating CDB Resource Plan Directives for a PDB

• Deleting CDB Resource Plan Directives for a PDB

• Updating the Default Directive for PDBs in a CDB Resource Plan

• Updating the Default Directive for Maintenance Tasks in a CDB Resource Plan

• Deleting a CDB Resource Plan

Modifying a PDB Resource Plan

You can use the DBMS_RESOURCE_MANAGER package to modify a PDB resource plan in the same way you would modify the resource plan for a non-CDB.

Before modifying a PDB resource plan, complete the prerequisites described in "Prerequisites for Using Resource Manager with a CDB".

To modify a PDB resource plan: 

1. In SQL*Plus, ensure that the current container is a PDB.

   See "Accessing a Container in a CDB with SQL*Plus".

2. Create a pending area:

   exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
   

3. Modify the PDB resource plan by completing one or more of the following tasks:

   • Update a consumer group using the UPDATE_CONSUMER_GROUP procedure.

   • Delete a consumer group using the DELETE_CONSUMER_GROUP procedure.

   • Update a resource plan using the UPDATE_PLAN procedure.

   • Delete a resource plan using the DELETE_PLAN procedure.

   • Update a resource plan directive using the UPDATE_PLAN_DIRECTIVE procedure.

   • Delete a resource plan directive using the DELETE_PLAN_DIRECTIVE procedure.

   See "Maintaining Consumer Groups, Plans, and Directives" for instructions about completing these tasks.

4. Validate the pending area:

   exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
   

5. Submit the pending area:

   exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
   

Viewing CDB Resource Plans

This example uses the DBA_CDB_RSRC_PLANS view to display all of the CDB resource plans defined in the CDB.

Run the following query in the root:

COLUMN PLAN FORMAT A30
COLUMN STATUS FORMAT A10
COLUMN COMMENTS FORMAT A35
 
SELECT PLAN, STATUS, COMMENTS FROM DBA_CDB_RSRC_PLANS ORDER BY PLAN;

Your output looks similar to the following:

PLAN                           STATUS     COMMENTS
------------------------------ ---------- -----------------------------------
DEFAULT_CDB_PLAN                          Default CDB plan
DEFAULT_MAINTENANCE_PLAN                  Default CDB maintenance plan
NEWCDB_PLAN                               CDB plan for PDBs in newcdb
ORA$INTERNAL_CDB_PLAN                     Internal CDB plan

The DEFAULT_CDB_PLAN is a default CDB plan that is supplied with Oracle Database. You can use this default plan if it meets your requirements.

Viewing CDB Resource Plan Directives

This example uses the DBA_CDB_RSRC_PLAN_DIRECTIVES view to display all of the directives defined in all of the CDB resource plans in the CDB.

Run the following query in the root:

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;

Your output looks similar to the following:

                                                                        Parallel
                           Pluggable                        Utilization   Server
Plan                       Database                  Shares       Limit    Limit
-------------------------- ------------------------- ------ ----------- --------
DEFAULT_CDB_PLAN           ORA$DEFAULT_PDB_DIRECTIVE      1         100      100
DEFAULT_CDB_PLAN           ORA$AUTOTASK                              90      100
DEFAULT_MAINTENANCE_PLAN   ORA$DEFAULT_PDB_DIRECTIVE      1         100      100
DEFAULT_MAINTENANCE_PLAN   ORA$AUTOTASK                              90      100
NEWCDB_PLAN                SALESPDB                       3
NEWCDB_PLAN                HRPDB                          1          70       70
NEWCDB_PLAN                ORA$DEFAULT_PDB_DIRECTIVE      2          40       50
NEWCDB_PLAN                ORA$AUTOTASK                   2          60       75
NEWCDB_PLAN                SERVICESPDB                    3
ORA$INTERNAL_CDB_PLAN      ORA$DEFAULT_PDB_DIRECTIVE
ORA$INTERNAL_CDB_PLAN      ORA$AUTOTASK

The DEFAULT_CDB_PLAN is a default CDB plan that is supplied with Oracle Database. You can use this default plan if it meets your requirements.

This output shows the directives for the newcdb_plan created in "Creating a CDB Resource Plan: A Scenario" and modified in "Managing a CDB Resource Plan".