Skip Headers
Oracle® Database Administrator's Guide
12c Release 1 (12.1)

E17636-21
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

44 Using Oracle Resource Manager for PDBs with SQL*Plus

This chapter describes using Oracle Resource Manager (Resource Manager) to allocate resources to pluggable databases (PDBs) in a multitenant container database (CDB). This chapter makes the following assumptions:

This chapter contains the following topics:

Note:

This chapter discusses using PL/SQL package procedures to administer the Resource Manager in a CDB. An easier way to administer the Resource Manager is with the graphical user interface of Oracle Enterprise Manager Cloud Control (Cloud Control). For instructions about administering Resource Manager in a CDB with Cloud Control, see Chapter 45, "Using Oracle Resource Manager for PDBs with Cloud Control" and the Cloud Control online help.

About Using Oracle Resource Manager with CDBs and PDBs

In a non-CDB, you can use 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.

In a CDB, Resource Manager can manage resources on two basic levels:

  • CDB level - Resource Manager can manage the workloads for multiple PDBs that are contending for system and CDB resources. You can specify how resources are allocated to PDBs, and you can limit the resource utilization of specific PDBs.

  • PDB level - Resource Manager can manage the workloads within each PDB.

Resource Manager allocates the resources in two steps:

  1. It allocates a portion of the system's resources to each PDB.

  2. In a specific PDB, it allocates a portion of system resources obtained in Step 1 to each session connected to the PDB.

Note:

All activity in the root is automatically managed by Resource Manager.

This section contains the following topics:

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

A CDB resource plan allocates resources to its PDBs according to its set of resource plan directives (directives). There is a parent-child relationship between a CDB resource plan and its directives. Each directive references one PDB, and no two directives for the currently active plan can reference the same PDB.

The directives control allocation of the following resources to the PDBs:

  • CPU

  • Parallel execution servers

A directive can control the allocation of resources to PDBs based on the share value that you specify for each PDB. A higher share value for a PDB results in more resources for that PDB. For example, you can specify that one PDB is allocated double the resources allocated to a second PDB by setting the share value for the first PDB twice as high as the share value for the second PDB.

You can also specify utilization limits for PDBs. The utilization limit for a PDB limits resource allocation to the PDB. For example, it can control how much CPU the PDB gets as a percentage of the total CPU available to the CDB.

You can use both shares and utilization limits together for precise control over the resources allocated to each PDB in a CDB. The following sections provide more information about shares and utilization limits.

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 follows
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 follows
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 follows
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.

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.

Note:

Table 44-4 describes parameter values for PL/SQL procedures. The parameter values described in the "CDB Resource Plan Requirements" column are for the CREATE_CDB_PLAN_DIRECTIVE procedure. The parameter values described in the "Results When Requirements Are Not Met" column are for the CREATE_PLAN_DIRECTIVE procedure.

Table 44-4 CDB Resource Plan Requirements for PDB Resource Plans

Resource CDB Resource Plan Requirements Results When Requirements Are Not Met

CPU

One of the following requirements must be met:

  • A share value must be specified for the PDB using the shares parameter.

  • A utilization limit for CPU below 100 must be specified for the PDB using the utilization_limit parameter.

These values can be set in a directive for the specific PDB or in a default directive.

The CPU allocation policy of the PDB resource plan is not enforced.

The CPU limit specified by the utilization_limit parameter in the PDB resource plan is not enforced.

Parallel execution servers

One of the following requirements must be met:

  • A share value must be specified for the PDB using the shares parameter.

  • A utilization limit for CPU below 100 must be specified for the PDB using the utilization_limit parameter.

  • A parallel server limit below 100 must be specified for the PDB using the parallel_server_limit parameter.

These values can be set in a directive for the specific PDB or in a default directive.

The parallel execution server allocation policy of the PDB resource plan is not enforced.

The parallel server limit specified by the parallel_server_limit parameter in the PDB resource plan is not enforced.


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 follows
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

Prerequisites for Using Resource Manager with a CDB

Before you can use Resource Manager with a CDB, the following prerequisites must be met:

Creating a CDB Resource Plan

You use the DBMS_RESOURCE_MANAGER package to create a CDB resource plan and define the directives for the plan. The general steps for creating a CDB resource plan are the following:

  1. Create the pending area using the CREATE_PENDING_AREA procedure.

  2. Create the CDB resource plan using the CREATE_CDB_PLAN procedure.

  3. Create directives for the PDBs using the CREATE_CDB_PLAN_DIRECTIVE procedure.

  4. (Optional) Update the default PDB directive using the UPDATE_CDB_DEFAULT_DIRECTIVE procedure.

  5. (Optional) Update the default autotask directive using the UPDATE_CDB_AUTOTASK_DIRECTIVE procedure.

  6. Validate the pending area using the VALIDATE_PENDING_AREA procedure.

  7. Submit the pending area using the SUBMIT_PENDING_AREA procedure.

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-5 Attributes for PDB Directives in a CDB Resource Plan

Directive Attribute Description

shares

Resource allocation share for CPU and parallel execution server resources. See "Shares for Allocating Resources to PDBs".

utilization_limit

Resource utilization limit for CPU. See "Utilization Limits for PDBs".

parallel_server_limit

Maximum percentage of parallel execution servers that a PDB can use.

When the parallel_server_limit directive is specified for a PDB, the limit is 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. See "Utilization Limits for PDBs".


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

Table 44-6 Sample Directives for PDBs in a CDB Resource Plan

PDB shares Directive utilization_limit Directive parallel_server_limit Directive

salespdb

3

Unlimited

Unlimited

servicespdb

3

Unlimited

Unlimited

hrpdb

1

70

70

Default

1

50

50

Autotask

1

75

75


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 and Disabling a CDB Resource Plan

This section contains the following topics:

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".

Creating a PDB Resource Plan

A CDB resource plan allocates a portion of the system's resources to a PDB. A PDB resource plan determines how this portion is allocated within the PDB. You create a PDB resource plan in the same way that you create a resource plan for a non-CDB. You use procedures in the DBMS_RESOURCE_MANAGER PL/SQL package to create the plan.

The following is a summary of the steps required to create a PDB resource plan:

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

  2. Create a pending area using the CREATE_PENDING_AREA procedure.

  3. Create, modify, or delete consumer groups using the CREATE_CONSUMER_GROUP procedure.

  4. Map sessions to consumer groups using the SET_CONSUMER_GROUP_MAPPING procedure.

  5. Create the PDB resource plan using the CREATE_PLAN procedure.

  6. Create PDB resource plan directives using the CREATE_PLAN_DIRECTIVE procedure.

  7. Validate the pending area using the VALIDATE_PENDING_AREA procedure.

  8. Submit the pending area using the SUBMIT_PENDING_AREA procedure.

Ensure that the current container is a PDB and that the user has the required privileges when you complete these steps. See "Creating a Complex Resource Plan" for detailed information about completing these steps.

You also have the option of creating a simple resource plan that is adequate for many situations using the CREATE_SIMPLE_PLAN procedure. See "Creating a Simple Resource Plan".

Note:

Some restrictions apply to PDB resource plans. See "PDB Resource Plans" for information.

Enabling and Disabling a PDB Resource Plan

This section contains the following topics:

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.

Example 44-1 Enabling a PDB Resource Plan

The following example sets the PDB resource plan to salespdb_plan.

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.

Example 44-2 Disabling a PDB Resource Plan

The following example disables the PDB resource plan.

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';

Maintaining Plans and Directives in a CDB

This section provides instructions for maintaining CDB resource plans, the default directive for PDBs, the autotask directive, and PDB resource plans. You perform maintenance tasks using the DBMS_RESOURCE_MANAGER PL/SQL package.

This section contains the following topics:

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

You can update a CDB resource plan to change its comment using the UPDATE_CDB_PLAN procedure.

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

To update 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. Create a pending area:

    exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
  3. Run the UPDATE_CDB_PLAN procedure, and enter a new comment in the new_comment parameter.

    For example, the following procedure changes the comment for the newcdb_plan CDB resource plan:

    BEGIN
      DBMS_RESOURCE_MANAGER.UPDATE_CDB_PLAN(
        plan        => 'newcdb_plan',
        new_comment => 'CDB plan for PDBs in newcdb');
    END;
    /
    
  4. Validate the pending area:

    exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    
  5. Submit the pending area:

    exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    

Creating New CDB Resource Plan Directives for a PDB

When you create a PDB in a CDB, you can create a CDB resource plan directive for the PDB using the CREATE_CDB_PLAN_DIRECTIVE procedure. The directive specifies how resources are allocated to the new PDB.

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

To create a new CDB resource plan directive for a PDB: 

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

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

  2. Create a pending area:

    exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
  3. Run the CREATE_CDB_PLAN_DIRECTIVE procedure, and specify the appropriate values for the new PDB.

    For example, the following procedure allocates resources to a PDB named operpdb in the newcdb_plan CDB resource plan:

    BEGIN
      DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE(
        plan                  => 'newcdb_plan', 
        pluggable_database    => 'operpdb', 
        shares                => 1, 
        utilization_limit     => 20,
        parallel_server_limit => 30);
    END;
    /
    
  4. Validate the pending area:

    exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    
  5. Submit the pending area:

    exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    

Updating CDB Resource Plan Directives for a PDB

You can update the CDB resource plan directive for a PDB using the UPDATE_CDB_PLAN_DIRECTIVE procedure. The directive specifies how resources are allocated to the PDB.

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

To update a CDB resource plan directive for a PDB: 

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

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

  2. Create a pending area:

    exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
  3. Run the UPDATE_CDB_PLAN_DIRECTIVE procedure, and specify the new resource allocation values for the PDB.

    For example, the following procedure updates the resource allocation to a PDB named operpdb in the newcdb_plan CDB resource plan:

    BEGIN
      DBMS_RESOURCE_MANAGER.UPDATE_CDB_PLAN_DIRECTIVE(
        plan                      => 'newcdb_plan', 
        pluggable_database        => 'operpdb', 
        new_shares                => 1, 
        new_utilization_limit     => 10,
        new_parallel_server_limit => 20);
    END;
    /
    
  4. Validate the pending area:

    exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    
  5. Submit the pending area:

    exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    

Deleting CDB Resource Plan Directives for a PDB

You can delete the CDB resource plan directive for a PDB using the DELETE_CDB_PLAN_DIRECTIVE procedure. You might delete the directive for a PDB if you unplug or drop the PDB. However, you can retain the directive, and if the PDB is plugged into the CDB in the future, the existing directive applies to the PDB.

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

To delete a CDB resource plan directive for a PDB: 

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

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

  2. Create a pending area:

    exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
  3. Run the DELETE_CDB_PLAN_DIRECTIVE procedure, and specify the CDB resource plan and the PDB.

    For example, the following procedure deletes the directive for a PDB named operpdb in the newcdb_plan CDB resource plan:

    BEGIN
      DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN_DIRECTIVE(
        plan               => 'newcdb_plan', 
        pluggable_database => 'operpdb');
    END;
    /
    
  4. Validate the pending area:

    exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    
  5. Submit the pending area:

    exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    

Updating the Default Directive for PDBs in a CDB Resource Plan

You can update the default directive for PDBs in a CDB resource plan 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.

Before updating the default directive for PDBs in a CDB resource plan, complete the prerequisites described in "Prerequisites for Using Resource Manager with a CDB".

To update the default directive for PDBs in 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. Create a pending area:

    exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
  3. Run the UPDATE_CDB_DEFAULT_DIRECTIVE procedure, and specify the appropriate default resource allocation values.

    For example, the following procedure updates the default directive for PDBs in the newcdb_plan CDB resource plan:

    BEGIN
      DBMS_RESOURCE_MANAGER.UPDATE_CDB_DEFAULT_DIRECTIVE(
        plan                  => 'newcdb_plan', 
        new_shares            => 2, 
        new_utilization_limit => 40);
    END;
    /
    
  4. Validate the pending area:

    exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    
  5. Submit the pending area:

    exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    

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

You can update the autotask directive in a CDB resource plan using the UPDATE_CDB_AUTOTASK_DIRECTIVE procedure. The autotask directive applies to automatic maintenance tasks that are run in the root maintenance window.

Before updating the default directive for maintenance tasks in a CDB resource plan, complete the prerequisites described in "Prerequisites for Using Resource Manager with a CDB".

To update the autotask directive for maintenance tasks in 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. Create a pending area:

    exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
  3. Run the UPDATE_CDB_AUTOTASK_DIRECTIVE procedure, and specify the appropriate autotask resource allocation values.

    For example, the following procedure updates the autotask directive for maintenance tasks in the newcdb_plan CDB resource plan:

    BEGIN
      DBMS_RESOURCE_MANAGER.UPDATE_CDB_AUTOTASK_DIRECTIVE(
        plan                  => 'newcdb_plan', 
        new_shares            => 2, 
        new_utilization_limit => 60);
    END;
    /
    
  4. Validate the pending area:

    exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    
  5. Submit the pending area:

    exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    

Deleting a CDB Resource Plan

You can delete a CDB resource plan using the DELETE_CDB_PLAN procedure. The resource plan must be disabled. You might delete a CDB resource plan if the plan is no longer needed. You can enable a different CDB resource plan, or you can disable Resource Manager for the CDB.

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

To delete 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. Create a pending area:

    exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
    
  3. Run the DELETE_CDB_PLAN procedure, and specify the CDB resource plan.

    For example, the following procedure deletes the newcdb_plan CDB resource plan:

    BEGIN
      DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN(
        plan => 'newcdb_plan');
    END;
    /
    
  4. Validate the pending area:

    exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
    
  5. Submit the pending area:

    exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
    

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 Information About Plans and Directives in a CDB

This section provides instructions for viewing information about CDB resource plans, CDB resource plan directives, and predefined resource plans in a CDB.

This section contains the following topics:

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.

Note:

Plans in the pending area have a status of PENDING. Plans in the pending area are being edited. Any plan that is not in the pending area has a NULL status.

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".

Note:

The ORA$DEFAULT_PDB_DIRECTIVE is the default directive for PDBs. See "The Default Directive for PDBs".