21 Using Oracle Resource Manager for PDBs
Use PL/SQL package procedures to administer Oracle Resource Manager (Resource Manager) to allocate resources to pluggable databases (PDBs) in a multitenant container database (CDB).
This chapter assumes that you meet the following prerequisites:
-
You understand how to configure and manage a CDB.
-
You understand how to use Resource Manager to allocate resources in a non-CDB.
Note:
-
You can complete the tasks in this chapter using SQL*Plus or Oracle SQL Developer.
-
You can also administer the Resource Manager with the graphical user interface of Oracle Enterprise Manager Cloud Control (Cloud Control).
-
For simplicity, this chapter refers to PDBs, application roots, and application PDBs as “PDBs.”
This chapter contains the following topics:
- Overview of Oracle Resource Manager in a Multitenant Environment
In a CDB, workloads within multiple PDBs can compete for system and CDB resources. Resource plans solve this problem. - Managing 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. - Managing PDB Resource Plans
You can create, enable, and modify resource plans for individual PDBs. - Monitoring PDBs Managed by Oracle Database Resource Manager
A set of dynamic performance views enables you to monitor the results of your Oracle Database Resource Manager settings for PDBs.
See Also:
-
"Using Oracle Resource Manager for PDBs with Cloud Control" to learn about administering Resource Manager in a CDB with Cloud Control
-
Oracle Database Administrator’s Guide to learn more about Resource Manager
Parent topic: Using Oracle Features in a Multitenant Environment
Overview of Oracle Resource Manager in a Multitenant Environment
In a CDB, workloads within multiple PDBs can compete for system and CDB resources. Resource plans solve this problem.
In a multitenant environment, Resource Manager operates on two 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. The principal tool is a CDB resource plan.
-
PDB level
Resource Manager can manage the workloads within each PDB. The principal tool is a PDB resource plan.
Resource Manager allocates the resources in two steps:
-
It allocates a portion of the system's resources to each PDB.
-
In a specific PDB, it allocates a portion of system resources obtained in the preceding step to each session connected to the PDB.
Note:
Resource Manager manages activity in the root automatically.
To use Resource Manager in a multitenant environment, you must meet the following prerequisites:
-
The CDB must exist and must contain PDBs.
-
To complete a task that uses the
DBMS_RESOURCE_MANAGER
package, you must haveADMINISTER_RESOURCE_MANAGER
system privilege.
This section contains the following topics:
- Purpose of Resource Management in a Multitenant Environment
Resource Manager can provide more efficient use of resources for a CDB. - Overview of Resource Plan Directives
A CDB resource plan allocates resources to its PDBs according to its set of resource plan directives (directives). - Background and Administrative Tasks and Consumer Groups
In a CDB, background and administrative tasks map to the Resource Manager consumer groups that run them optimally. - Initialization Parameters for Multitenant Resource Management
Use initialization parameters for control memory and I/O in a PDB.
See Also:
-
"Creating and Configuring a CDB" and "Creating and Removing PDBs and Application Containers"
-
Oracle Database Administrator’s Guide to learn more about the
DBMS_RESOURCE_MANAGER
package
Parent topic: Using Oracle Resource Manager for PDBs
Purpose of Resource Management in a Multitenant Environment
Resource Manager can provide more efficient use of resources 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 memory usage of a particular PDB
-
Specify the amount of memory guaranteed for a particular PDB
-
Specify the maximum amount of memory a particular PDB can use
-
Use PDB performance profiles for different sets of PDB
A performance profile for a set of PDBs can specify shares of system resources, CPU usage, and number of parallel execution servers. PDB performance profiles enable you to manage resources for large numbers of PDBs by specifying Resource Manager directives for profiles instead of individual PDBs.
-
Limit the resource usage of different sessions connected to a single PDB
-
Limit the I/O generated by specific PDBs
-
Monitor the resource usage of PDBs
Overview of Resource Plan Directives
A CDB resource plan allocates resources to its PDBs according to its set of resource plan directives (directives).
A parent-child relationship exists between a CDB resource plan and its resource plan directives. Each directive references either a set of PDBs in a performance profile, or a single PDB.
You can specify directives for both individual PDBs and for PDB performance profiles in the same CDB. No two directives for the currently active plan can reference the same PDB or the same PDB performance profile.
This section contains the following topics:
- PDB Performance Profiles
A PDB performance profile configures resource plan directives for a set of PDBs that have the same priorities and resource controls. - Resource Plan Directives
Directives control allocation of CPU and parallel execution servers.
PDB Performance Profiles
A PDB performance profile configures resource plan directives for a set of PDBs that have the same priorities and resource controls.
For example, you might create a performance profiles called Gold, Silver, and Bronze. Each profile specifies a different set of directives depending on the importance of the type of PDB. Gold PDBs are more mission critical than Silver PDBs, which are more mission critical than Bronze PDBs. A PDB specifies its performance profile with the DB_PERFORMANCE_PROFILE
initialization parameter.
You can use PDB lockdown profiles to specify PDB initialization parameters that control resources, such as SGA_TARGET
and PGA_AGGREGATE_LIMIT
. A lockdown profile prevents the PDB administrator from modifying the settings.
Oracle recommends using matching names for performance profiles and lockdown profiles. To prevent PDB owners from switching profiles, Oracle recommends putting the PDB performance profile in the PDB lockdown profile.
Parent topic: Overview of Resource Plan Directives
Resource Plan Directives
Directives control allocation of CPU and parallel execution servers.
A directive can control the allocation of resources to PDBs based on the share value that you specify for each PDB or PDB performance profile. A higher share value results in more resources. 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. Similarly, you can specify that one PDB performance profile is allocated double the resources allocated to a second PDB performance profile by setting the share value for the first PDB performance profile twice as high as the share value for the second PDB performance profile. The settings apply to the set of PDBs that use each profile.
You can also specify utilization limits for PDBs and PDB performance profiles. The limit controls allocation to the PDB or performance profile. For example, the limit can control how much CPU a 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 and PDB performance profile in a CDB.
See Also:
"About Restricting PDB Users for Enhanced Security" for more information about PDB lockdown profiles
Parent topic: Overview of Resource Plan Directives
Background and Administrative Tasks and Consumer Groups
In a CDB, background and administrative tasks map 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 CDB root, then the task maps to a consumer group in the CDB root. If the task starts in a PDB, then the task maps to a consumer group in the PDB.
-
Many maintenance and administrative tasks automatically map to a consumer group.
For example, automated maintenance tasks map to
ORA$AUTOTASK
. In certain cases, the tasks map to a consumer group, but the mapping is modifiable. Such tasks include RMAN backup, RMAN image copy, Oracle Data Pump, and In-Memory population.
Note:
Oracle Database Administrator’s Guide to learn more about the mapping rules for predefined consumer groups
Initialization Parameters for Multitenant Resource Management
Use initialization parameters for control memory and I/O in a PDB.
This section contains the following topics:
- Memory-Related Initialization Parameters for PDBs
Several initialization parameters control the memory usage of a PDB. - I/O-Related Initialization Parameters for PDBs
TheMAX_IOPS
andMAX_MBPS
initialization parameters limit the disk I/O generated by a PDB. - CPU-Related Initialization Parameters for PDBs
TheCPU_COUNT
initialization parameter specifies the number of CPUs available for Oracle Database to use.
Memory-Related Initialization Parameters for PDBs
Several initialization parameters control the memory usage of a PDB.
When the PDB is the current container, the initialization parameters in the following table control the memory usage of the current PDB. When one or more of these parameters is set for a PDB, ensure that the CDB and the other PDBs have sufficient memory for their operations. The initialization parameters control the memory usage of PDBs only if the following conditions are met:
-
The
NONCDB_COMPATIBLE
initialization parameter is set tofalse
in the CDB root. -
The
MEMORY_TARGET
initialization parameter is not set or is set to0
(zero) in the CDB root.
Table 21-1 Initialization Parameters That Control the Memory Usage of PDBs
Initialization Parameter | Description |
---|---|
|
Sets the minimum, guaranteed buffer cache space for the PDB. The following requirements must be met:
These requirements do not apply if the When the
|
|
Sets the minimum, guaranteed shared pool space for the PDB. The following requirements must be met:
These requirements do not apply if the When the
|
|
Sets the minimum SGA size for the PDB. The following requirements must be met:
These requirements do not apply if the |
|
Sets the maximum SGA size for the PDB. The |
|
Sets the maximum PGA size for the PDB. The following requirements must be met:
|
|
Sets the target aggregate PGA size for the PDB. The following requirements must be met:
|
Example 21-1 Setting the Maximum Aggregate PGA Memory Available for a PDB
With the PDB as the current container, run the following SQL statement to set the PGA_AGGREGATE_LIMIT
initialization parameter both in memory and in the SPFILE to 90 MB:
ALTER SYSTEM SET PGA_AGGREGATE_LIMIT = 90M SCOPE = BOTH;
Example 21-2 Setting the Minimum SGA Size for a PDB
iWith the PDB as the current container, run the following SQL statement to set the SGA_MIN_SIZE
initialization parameter both in memory and in the SPFILE to 500 MB:
ALTER SYSTEM SET SGA_MIN_SIZE = 500M SCOPE = BOTH;
I/O-Related Initialization Parameters for PDBs
The MAX_IOPS
and MAX_MBPS
initialization parameters limit the disk I/O generated by a PDB.
A large amount of disk I/O can cause poor performance. Several factors can result in excess disk I/O, such as poorly designed SQL or index and table scans in high-volume transactions. If one PDB is generating a large amount of disk I/O, then it can degrade the performance of other PDBs in the same CDB.
Use one or both of the following initialization parameters to limit the I/O generated by a specific PDB:
-
The
MAX_IOPS
initialization parameter limits the number of I/O operations for each second. -
The
MAX_MBPS
initialization parameter limits the megabytes for I/O operations for each second.
If you set both preceding initialization parameters for a single PDB, then Oracle Database enforces both limits. Note that these limits are not enforced for Oracle Exadata, which uses I/O Resource Management (IORM) to manage I/Os between PDBs.
If these initialization parameters are set with the CDB root as the current container, then the values become the default values for all containers in the CDB. If they are set with an application root as the current container, then the values become the default values for all application PDBs in the application container. When they are set with a PDB or application PDB as the current container, then the settings take precedence over the default settings in the CDB root or the application root. These parameters cannot be set in a non-CDB.
The default for both initialization parameters is 0 (zero). If these initialization parameters are set to 0 (zero) in a PDB, and the CDB root is set to 0, then there is no I/O limit for the PDB. If these initialization parameters are set to 0 (zero) in an application PDB, and its application root is set to 0, then there is no I/O limit for the application PDB.
Critical I/O operations, such as ones for the control file and password file, are exempted from the limit and continue to run even if the limit is reached. However, all I/O operations, including critical I/O operations, are counted when the number of I/O operations and the megabytes for I/O operations are calculated.
You can use the DBA_HIST_RSRC_PDB_METRIC
view to calculate a reasonable I/O limit for a PDB. Consider the values in the following columns when calculating a limit: IOPS
, IOMBPS
, IOPS_THROTTLE_EXEMPT
, and IOMBPS_THROTTLE_EXEMPT
. The rsmgr:io rate limit
wait event indicates that a limit was reached.
Example 21-3 Limiting the I/O Generated by a PDB
With the PDB as the current container, run the following SQL statement to set the MAX_IOPS
initialization parameter both in memory and in the SPFILE to a limit of 1,000 I/O operations for each second:
ALTER SYSTEM SET MAX_IOPS = 1000 SCOPE = BOTH;
Example 21-4 Limiting the Megabytes of I/O Generated by a PDB
With the PDB as the current container, run the following SQL statement to set the MAX_MBPS
initialization parameter both in memory and in the SPFILE to a limit of 200 MB of I/O for each second:
ALTER SYSTEM SET MAX_MBPS = 200 SCOPE = BOTH;
See Also:
-
Oracle Database Reference for more information about the
MAX_IOPS
initialization parameter -
Oracle Database Reference for more information about the
MAX_MBPS
initialization parameter
CPU-Related Initialization Parameters for PDBs
The CPU_COUNT
initialization parameter specifies the number of CPUs available for Oracle Database to use.
Instance caging is a technique that uses an initialization parameter to limit the number of CPUs that an instance can use simultaneously. You can set CPU_COUNT
at the PDB level. If Resource Manager is enabled, then the PDB is “caged” (restricted) to the number of CPUs specified by CPU_COUNT
.
CPU_COUNT
works the same way as the utilization_limit
directive in the CDB plan. However, the CPU_COUNT
limit is expressed in terms of number of CPUs rather than utilization percentage. If both the utilization_limit
and CPU_COUNT
are specified, then the lower limit is enforced.
CPU_COUNT
is advantageous because when the PDB is plugged into a new container, the CPU_COUNT
setting remains with the plugged-in PDB. Also, Oracle Database uses the CPU_COUNT
setting for a PDB to derive many other PDB parameters, such as those for parallel execution.
Managing 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
Create CDB resource plans that allocate shares and resource limits for PDBs. - Creating a CDB Resource Plan for Managing PDBs
To create a CDB resource plan for individual PDBs and define the directives for the plan, use theDBMS_RESOURCE_MANAGER
package. - Creating a CDB Resource Plan for Managing PDBs: Scenario
This scenario illustrates each of the steps involved in creating a CDB resource plan for individual PDBs. - Creating a CDB Resource Plan with PDB Performance Profiles
Use theDBMS_RESOURCE_MANAGER
package to create a CDB resource plan for PDB performance profiles and define the directives for the plan. Each PDB that uses a profile adopts the CDB resource plan directive. - Creating a CDB Resource Plan for PDB Performance Profiles: Scenario
This scenario illustrates the steps involved in creating a CDB resource plan for PDB performance profiles. - Enabling a CDB Resource Plan
You enable the Resource Manager for a CDB by setting theRESOURCE_MANAGER_PLAN
initialization parameter in the root. - Modifying a CDB Resource Plan
Modifying a CDB resource plan includes tasks such as updating the plan, creating, updating, or deleting plan directives for PDBs, and updating default directives. - Disabling a CDB Resource Plan
Disable the Resource Manager for a CDB by unsetting theRESOURCE_MANAGER_PLAN
initialization parameter in the CDB root. - Viewing Information About Plans and Directives in a CDB
You can view information about CDB resource plans, CDB resource plan directives, and predefined resource plans in a CDB.
Parent topic: Using Oracle Resource Manager for PDBs
About CDB Resource Plans
Create CDB resource plans that allocate shares and resource limits for PDBs.
This section contains the following topics:
- Shares for Allocating Resources to PDBs
To allocate resources among PDBs, assign a share value to each PDB or performance profile. A higher share value results in more guaranteed resources for a PDB or the PDBs that use the performance profile. - Utilization Limits for PDBs
A utilization limit restrains the system resource usage of a specific PDB or a specific PDB performance profile. - The Default Directive for PDBs
When you do not explicitly define directives for a PDB, the PDB uses the default directive for PDBs.
Parent topic: Managing CDB Resource Plans
Shares for Allocating Resources to PDBs
To allocate resources among PDBs, assign a share value to each PDB or performance profile. A higher share value results in more guaranteed resources for a PDB or the PDBs that use the performance profile.
Specify a share value for a PDB using the DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE
procedure and for a PDB performance profile using the DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE
procedure. In both cases, the shares
parameter specifies the share value for the PDB. Multiple PDBs can use the same PDB performance profile.
The following figure shows an example of three PDBs with share values specified for them in a CDB resource plan.
The preceding figure shows that the total number of shares is seven (3 plus 3 plus 1). The salespdb
and the servicespdb
PDB are each guaranteed 3/7 of the resources, while the hrpdb
PDB is guaranteed 1/7 of the resources. However, any PDB can use more than the guaranteed amount of a resource when no resource contention exists.
The following table shows the resource allocation to the PDBs in the preceding figure based on the share values. The table assumes that loads of the PDBs consume all system resources allocated.
Table 21-2 Resource Allocation for Sample PDBs
Resource | Resource Allocation | See Also |
---|---|---|
CPU |
The |
Oracle Database Administrator’s Guide for more information about this resource |
Parallel execution servers |
Queued parallel queries from the |
Oracle Database Administrator’s Guide for more information about this resource |
Parent topic: About CDB Resource Plans
Utilization Limits for PDBs
A utilization limit restrains the system resource usage of a specific PDB or a specific PDB performance profile.
You can specify utilization limits for CPU and parallel execution servers. Utilization limits for a PDB are set by the CDB resource plan.
The following table describes utilization limits for PDBs and the Resource Manager action taken when a PDB reaches a utilization limit. For limits specified with a PDB performance profile, the limit applies to every PDB that uses the PDB performance profile. For example, if pdb1
and pdb20
have a performance profile BRONZE
, and if BRONZE
has a limit set to 10%, then pdb1
has a 10% limit and pdb20
has a 10% limit.
Table 21-3 Utilization Limits for PDBs
Resource | Resource Utilization Limit | Resource Manager Action When Limit Is Reached |
---|---|---|
CPU |
The CPU utilization limit for sessions connected to a PDB is set by the You can also limit CPU for a PDB by setting the initialization parameter |
Resource Manager throttles the PDB sessions so that the CPU utilization for the PDB does not exceed the utilization limit. |
Parallel execution servers |
You can limit the number of parallel execution servers in a PDB by means of parallel statement queuing. The limit is a “queuing point” because the database queues parallel queries when the limit is reached. You can set the limit (queuing point) in either of the following ways:
If the limit is set in both preceding ways, then the lower limit of the two is used. See Oracle Database Reference for the default value for Note: Oracle recommends using the |
Resource Manager queues parallel queries when the number of parallel execution servers used by the PDB would exceed the limit. Note: In a CDB, parallel statements are queued based on the |
The following figure shows an example of three PDBs with shares and utilization limits specified for them in a CDB resource plan.
Figure 21-2 Shares and Utilization Limits in a CDB Resource Plan
Description of "Figure 21-2 Shares and Utilization Limits in a CDB Resource Plan"
The preceding figure 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%.
Note:
This scenario assumes that the PARALLEL_SERVERS_TARGET
initialization parameter does not specify a lower limit in a PDB. When the PARALLEL_SERVERS_TARGET
initialization parameter specifies a lower limit for parallel execution servers in a PDB, the lower limit is used.
See Also:
-
Oracle Database Reference to learn about
CPU_COUNT
Parent topic: About CDB Resource Plans
The Default Directive for PDBs
When you do not explicitly define directives for a PDB, the PDB uses the default directive for PDBs.
The following table shows the attributes of the initial default directive for PDBs.
Table 21-4 Initial Default Directive Attributes for PDBs
Directive Attribute | Value |
---|---|
|
1 |
|
100 |
|
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 21-3 shows an example of the default directive in a CDB resource plan.
Figure 21-3 Default Directive in a CDB Resource Plan
Description of "Figure 21-3 Default Directive in a CDB Resource Plan"
Figure 21-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 21-3 shows the PDBs marketingpdb
and testingpdb
using the default PDB directive. Therefore, marketingpdb
and testingpdb
each get 1 share and utilization limits of 50.
See Also:
-
"Updating the Default Directive for PDBs in a CDB Resource Plan"
-
Oracle Database Administrator’s Guide for information about the parallel server limit
Parent topic: About CDB Resource Plans
Creating a CDB Resource Plan for Managing PDBs
To create a CDB resource plan for individual PDBs and define the directives for the plan, use the DBMS_RESOURCE_MANAGER
package.
The general steps for creating a CDB resource plan for individual PDBs are the following:
- Create the pending area using the
CREATE_PENDING_AREA
procedure. - Create the CDB resource plan using the
CREATE_CDB_PLAN
procedure. - Create directives for the PDBs using the
CREATE_CDB_PLAN_DIRECTIVE
procedure. - (Optional) Update the default PDB directive using the
UPDATE_CDB_DEFAULT_DIRECTIVE
procedure. - Validate the pending area using the
VALIDATE_PENDING_AREA
procedure. - Submit the pending area using the
SUBMIT_PENDING_AREA
procedure.
Parent topic: Managing CDB Resource Plans
Creating a CDB Resource Plan for Managing PDBs: Scenario
This scenario illustrates each of the steps involved in creating a CDB resource plan for individual PDBs.
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 21-5 describes the types of directives in the plan.
Table 21-5 Attributes for PDB Directives in a CDB Resource Plan
Directive Attribute | Description | See Also |
---|---|---|
|
Resource allocation share for CPU and parallel execution server resources. |
|
|
Resource utilization limit for CPU. |
|
|
Maximum percentage of parallel execution servers that a PDB can use before queuing parallel statements. When the Note: Oracle recommends using the |
Table 21-6 describes how the CDB resource plan allocates resources to its PDBs using the directive attributes described in Table 21-5.
Table 21-6 Sample Directives for PDBs in a CDB Resource Plan
PDB | shares Directive | utilization_limit Directive | parallel_server_limit Directive |
---|---|---|---|
|
3 |
Unlimited |
Unlimited |
|
3 |
Unlimited |
Unlimited |
|
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.
To create a CDB resource plan:
-
Create a pending area using the
CREATE_PENDING_AREA
procedure:exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
Create a CDB resource plan named
newcdb_plan
using theCREATE_CDB_PLAN
procedure:BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN( plan => 'newcdb_plan', comment => 'CDB resource plan for newcdb'); END; /
-
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 21-6 describes the directives for the
salespdb
,servicespdb
, andhrpdb
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.
-
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 21-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; /
-
Validate the pending area using the
VALIDATE_PENDING_AREA
procedure:exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area using the
SUBMIT_PENDING_AREA
procedure:exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
Parent topic: Managing CDB Resource Plans
Creating a CDB Resource Plan with PDB Performance Profiles
Use the DBMS_RESOURCE_MANAGER
package to create a CDB resource plan for PDB performance profiles and define the directives for the plan. Each PDB that uses a profile adopts the CDB resource plan directive.
The general steps for creating a CDB resource plan with PDB performance profiles are the following:
- Create the pending area using the
CREATE_PENDING_AREA
procedure. - Create the CDB resource plan using the
CREATE_CDB_PLAN
procedure. - Create directives for the PDB performance profiles using the
CREATE_CDB_PROFILE_DIRECTIVE
procedure. - (Optional) Update the default PDB directive using the
UPDATE_CDB_DEFAULT_DIRECTIVE
procedure. - Validate the pending area using the
VALIDATE_PENDING_AREA
procedure. - Submit the pending area using the
SUBMIT_PENDING_AREA
procedure. - For each PDB that will use a profile, set the
DB_PERFORMANCE_PROFILE
initialization parameter and specify the profile name.
Parent topic: Managing CDB Resource Plans
Creating a CDB Resource Plan for PDB Performance Profiles: Scenario
This scenario illustrates the steps involved in creating a CDB resource plan for PDB performance profiles.
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 performance profile. In this scenario, you also update the default directive and the AutoTask directive.
In the CDB resource plan, you give each profile a name. In each PDB, you set the DB_PERFORMANCE_PROFILE
initialization parameter to specify which PDB performance profile the PDB uses.
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. The following table describes the types of directives in the plan.
Table 21-7 Attributes for PDB Performance Profile Directives in a CDB Resource Plan
Directive Attribute | Description | See Also |
---|---|---|
|
Resource allocation share for CPU and parallel execution server resources. |
"Shares for Allocating Resources to PDBs" |
|
Resource utilization limit for CPU. |
"Utilization Limits for PDBs" |
|
Maximum percentage of parallel execution servers that a PDB can use. When the |
The following table describes how the CDB resource plan allocates resources to its PDB performance profiles using the directive attributes described in Table 21-7.
Table 21-8 Sample Directives for PDB Performance Profiles in a CDB Resource Plan
PDB | shares Directive | utilization_limit Directive | parallel_server_limit Directive |
---|---|---|---|
|
3 |
Unlimited |
Unlimited |
|
2 |
40 |
40 |
|
1 |
20 |
20 |
Default |
1 |
10 |
10 |
AutoTask |
2 |
60 |
60 |
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.
To create a CDB resource plan for PDB performance profiles:
-
For each PDB that will use a profile, set the
DB_PERFORMANCE_PROFILE
initialization parameter to the name of the profile that the PDB will use.-
Run an
ALTER SYSTEM
statement to set the parameter.For example, with the PDB as the current container, run the following SQL statement:
ALTER SYSTEM SET DB_PERFORMANCE_PROFILE=gold SCOPE=spfile;
-
Close the PDB:
ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;
-
Open the PDB:
ALTER PLUGGABLE DATABASE OPEN;
-
-
Create a pending area using the
CREATE_PENDING_AREA
procedure:exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
Create a CDB resource plan named
newcdb_plan
using theCREATE_CDB_PLAN
procedure:BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN( plan => 'newcdb_plan', comment => 'CDB resource plan for newcdb'); END; /
-
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 21-6 describes the directives for the
gold
,silver
, andbronze
profiles in this scenario. Run the following procedures to create these directives:BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE( plan => 'newcdb_plan', profile => 'gold', shares => 3, utilization_limit => 100, parallel_server_limit => 100); END; / BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE( plan => 'newcdb_plan', profile => 'silver', shares => 2, utilization_limit => 40, parallel_server_limit => 40); END; / BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE( plan => 'newcdb_plan', profile => 'bronze', shares => 1, utilization_limit => 20, parallel_server_limit => 20); END; /
All other PDBs in this CDB use 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.
Table 21-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 => 10, new_parallel_server_limit => 10); END; /
-
Validate the pending area using the
VALIDATE_PENDING_AREA
procedure:exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area using the
SUBMIT_PENDING_AREA
procedure:exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
"The Default Directive for PDBs"Parent topic: Managing CDB Resource Plans
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.
Prerequisites
Before enabling a CDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To enable a CDB resource plan:
-
In SQL*Plus, ensure that the current container is the root.
-
Perform one of the following actions:
-
Use an
ALTER SYSTEM
statement to set theRESOURCE_MANAGER_PLAN
initialization parameter to the CDB resource plan.The following example sets the CDB resource plan to
newcdb_plan
using anALTER 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'
-
See Also:
-
Oracle Database Administrator’s Guide to learn how to schedule a CDB resource plan change with Oracle Scheduler
Parent topic: Managing CDB Resource Plans
Modifying a CDB Resource Plan
Modifying a CDB resource plan includes tasks such as updating the plan, creating, updating, or deleting plan directives for PDBs, and updating default directives.
This section contains the following topics:
- Updating a CDB Resource Plan
You can update a CDB resource plan to change its comment using theUPDATE_CDB_PLAN
procedure. - Managing CDB Resource Plan Directives for a PDB
You can create, update, and delete CDB resource plan directives for a PDB. - Managing CDB Resource Plan Directives for a PDB Performance Profile
You can create, update, and delete CDB resource plan directives for a PDB performance profile. - 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 theUPDATE_CDB_DEFAULT_DIRECTIVE
procedure. The default directive applies to PDBs for which specific directives have not been defined. - Updating the Default Directive for Maintenance Tasks in a CDB Resource Plan
You can update the AutoTask directive in a CDB resource plan using theUPDATE_CDB_AUTOTASK_DIRECTIVE
procedure. The AutoTask directive applies to automatic maintenance tasks that are run in the root maintenance window. - Deleting a CDB Resource Plan
You can delete a CDB resource plan using theDELETE_CDB_PLAN
procedure.
Parent topic: Managing CDB Resource Plans
Updating a CDB Resource Plan
You can update a CDB resource plan to change its comment using the UPDATE_CDB_PLAN
procedure.
Prerequisites
Before updating a CDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To update a CDB resource plan:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
Run the
UPDATE_CDB_PLAN
procedure, and enter a new comment in thenew_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; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
Parent topic: Modifying a CDB Resource Plan
Managing CDB Resource Plan Directives for a PDB
You can create, update, and delete CDB resource plan directives for a PDB.
This section contains the following topics:
- 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 theCREATE_CDB_PLAN_DIRECTIVE
procedure. The directive specifies how resources are allocated to the new PDB. - Updating CDB Resource Plan Directives for a PDB
You can update the CDB resource plan directive for a PDB using theUPDATE_CDB_PLAN_DIRECTIVE
procedure. The directive specifies how resources are allocated to the PDB. - Deleting CDB Resource Plan Directives for a PDB
You can delete the CDB resource plan directive for a PDB using theDELETE_CDB_PLAN_DIRECTIVE
procedure.
Parent topic: Modifying a CDB Resource Plan
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.
Prerequisites
Before creating a new CDB resource plan directive for a PDB, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To create a new CDB resource plan directive for a PDB:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
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 thenewcdb_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; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
Parent topic: Managing CDB Resource Plan Directives for a PDB
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.
Prerequisites
Before updating a CDB resource plan directive for a PDB, ensure that you meet the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To update a CDB resource plan directive for a PDB:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
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 thenewcdb_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; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
Parent topic: Managing CDB Resource Plan Directives for a PDB
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.
Prerequisites
Before deleting a CDB resource plan directive for a PDB, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To delete a CDB resource plan directive for a PDB:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
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 thenewcdb_plan
CDB resource plan:BEGIN DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN_DIRECTIVE( plan => 'newcdb_plan', pluggable_database => 'operpdb'); END; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
Parent topic: Managing CDB Resource Plan Directives for a PDB
Managing CDB Resource Plan Directives for a PDB Performance Profile
You can create, update, and delete CDB resource plan directives for a PDB performance profile.
This section contains the following topics:
- Creating New CDB Resource Plan Directives for a PDB Performance Profile
You can create a CDB resource plan directive for the a new PDB performance profile using theCREATE_CDB_PROFILE_DIRECTIVE
procedure. The directive specifies how resources are allocated to the all PDBs that use the new profile. - Updating CDB Resource Plan Directives for a PDB Performance Profile
Update the CDB resource plan directive for a PDB performance profile using theUPDATE_CDB_PROFILE_DIRECTIVE
procedure. The directive specifies how resources are allocated to the PDBs that use the PDB performance profile. - Deleting CDB Resource Plan Directives for a PDB Performance Profile
You can delete the CDB resource plan directive for a PDB performance profile using theDELETE_CDB_PROFILE_DIRECTIVE
procedure.
Parent topic: Modifying a CDB Resource Plan
Creating New CDB Resource Plan Directives for a PDB Performance Profile
You can create a CDB resource plan directive for the a new PDB performance profile using the CREATE_CDB_PROFILE_DIRECTIVE
procedure. The directive specifies how resources are allocated to the all PDBs that use the new profile.
Prerequisites
Before creating a new CDB resource plan directive for a PDB performance profile, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To create a new CDB resource plan directive for a PDB performance profile:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
Run the
CREATE_CDB_PROFILE_DIRECTIVE
procedure, and specify the appropriate values for the new PDB performance profile.For example, the following procedure allocates resources to a PDB performance profile named
copper
in thenewcdb_plan
CDB resource plan:BEGIN DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE( plan => 'newcdb_plan', profile => 'copper', shares => 1, utilization_limit => 20, parallel_server_limit => 30); END; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
Note:
For a PDB to use the new profile, the PDB must have theDB_PERFORMANCE_PROFILE
initialization parameter set to the profile name.
Updating CDB Resource Plan Directives for a PDB Performance Profile
Update the CDB resource plan directive for a PDB performance profile using the UPDATE_CDB_PROFILE_DIRECTIVE
procedure. The directive specifies how resources are allocated to the PDBs that use the PDB performance profile.
Before updating a CDB resource plan directive for a PDB performance profile, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To update a CDB resource plan directive for a PDB performance profile:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
Run the
UPDATE_CDB_PROFILE_DIRECTIVE
procedure, and specify the new resource allocation values for the PDB performance profile.For example, the following procedure updates the resource allocation for a PDB performance profile named
copper
in thenewcdb_plan
CDB resource plan:BEGIN DBMS_RESOURCE_MANAGER.UPDATE_CDB_PROFILE_DIRECTIVE( plan => 'newcdb_plan', profile => 'copper', new_shares => 1, new_utilization_limit => 10, new_parallel_server_limit => 20); END; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
Deleting CDB Resource Plan Directives for a PDB Performance Profile
You can delete the CDB resource plan directive for a PDB performance profile using the DELETE_CDB_PROFILE_DIRECTIVE
procedure.
If no PDBs use a performance profile, then you might delete the directive for the profile.
Prerequisites
Before deleting a CDB resource plan directive for a PDB performance profile, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To delete a CDB resource plan directive for a PDB performance profile:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
Run the
DELETE_CDB_PROFILE_DIRECTIVE
procedure, and specify the CDB resource plan and the PDB performance profile.For example, the following procedure deletes the directive for a PDB named
operpdb
in thenewcdb_plan
CDB resource plan:BEGIN DBMS_RESOURCE_MANAGER.DELETE_CDB_PLAN_DIRECTIVE( plan => 'newcdb_plan', profile => 'operpdb'); END; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
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.
Prerequisites
Before updating the default directive for PDBs in a CDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To update the default directive for PDBs in a CDB resource plan:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
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; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
-
See "The Default Directive for PDBs" for more information.
Parent topic: Modifying a CDB Resource Plan
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.
Prerequisites
Before updating the default directive for maintenance tasks in a CDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To update the AutoTask directive for maintenance tasks in a CDB resource plan:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
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; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
Parent topic: Modifying a CDB Resource Plan
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.
Prerequisites
Before deleting a CDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To delete a CDB resource plan:
-
In SQL*Plus, ensure that the current container is the root.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
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; /
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
Parent topic: Modifying a CDB Resource Plan
Disabling a CDB Resource Plan
Disable the Resource Manager for a CDB by unsetting the RESOURCE_MANAGER_PLAN
initialization parameter in the CDB root.
A CDB resource plan that specifies shares or utilization limits for PDBs is required to enable CPU management, both between PDBs and within a PDB. If a resource plan with shares or utilization limits is enabled for a PDB, and if the CDB resource plan is not specified, then the CDB resource plan is set to DEFAULT_CDB_PLAN
. This setting gives equal shares to all PDBs and specifies no utilization limits. To disable CPU resource management throughout the CDB, set RESOURCE_MANAGER_PLAN
to ORA$INTERNAL_CDB_PLAN
.
Prerequisites
Before disabling a CDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To disable a CDB resource plan:
-
In SQL*Plus, ensure that the current container is the root.
-
Perform one of the following actions:
-
Use an
ALTER SYSTEM
statement to unset theRESOURCE_MANAGER_PLAN
initialization parameter for the CDB.The following example unsets the
RESOURCE_MANAGER_PLAN
initialization parameter using anALTER 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 =
-
See Also:
-
Oracle Database Administrator’s Guide for information about starting up a database
Parent topic: Managing CDB Resource Plans
Viewing Information About Plans and Directives in a CDB
You can view 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
An example illustrates using theDBA_CDB_RSRC_PLANS
view to display all CDB resource plans defined in the CDB. - Viewing CDB Resource Plan Directives
An example illustrates using theDBA_CDB_RSRC_PLAN_DIRECTIVES
view to display all directives defined in all CDB resource plans in the CDB.
See Also:
Oracle Database Administrator’s Guide for information about monitoring Oracle Database Resource Manager
Parent topic: Managing CDB Resource Plans
Viewing CDB Resource Plans
An example illustrates using the DBA_CDB_RSRC_PLANS
view to display all CDB resource plans defined in the CDB.
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.
To view CDB resource plans:
-
Start SQL*Plus or SQL Developer, and log in to the CDB root.
-
Run the following query:
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
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.
See Also:
Parent topic: Viewing Information About Plans and Directives in a CDB
Viewing CDB Resource Plan Directives
An example illustrates using the DBA_CDB_RSRC_PLAN_DIRECTIVES
view to display all directives defined in all CDB resource plans in the CDB.
The DEFAULT_CDB_PLAN
is a default CDB plan that is supplied with Oracle Database. With DEFAULT_CDB_PLAN
, every PDB has 1 share and a utilization limit of 100. You can use this default plan if it meets your requirements. Note that ORA$DEFAULT_PDB_DIRECTIVE
is the default directive for PDBs.
To view CDB resource plan directives:
-
Start SQL*Plus or SQL Developer, and log in to the CDB root.
-
Run the following query:
COLUMN PLAN HEADING 'Plan' FORMAT A24 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 Utilization Server Plan Pluggable 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$AUTOTASK 90 100 DEFAULT_MAINTENANCE_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100 NEWCDB_PLAN HRPDB 1 70 70 NEWCDB_PLAN SALESPDB 3 100 100 NEWCDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 50 50 NEWCDB_PLAN ORA$AUTOTASK 1 75 75 NEWCDB_PLAN SERVICESPDB 3 100 100
The preceding output shows the directives for the
newcdb_plan
created in "Creating a CDB Resource Plan for Managing PDBs: Scenario" and modified in "Modifying a CDB Resource Plan".
See Also:
Parent topic: Viewing Information About Plans and Directives in a CDB
Managing PDB Resource Plans
You can create, enable, and modify resource plans for individual PDBs.
This section contains the following topics:
- About PDB Resource Plans
A PDB resource plan determines how the resources for a specific PDB are allocated to consumer groups within this PDB. - Creating a PDB Resource Plan
You create a PDB resource plan in the same way that you create a resource plan for a non-CDB. You use procedures in theDBMS_RESOURCE_MANAGER
PL/SQL package to create the plan. - Enabling a PDB Resource Plan
Enable a PDB resource plan by setting theRESOURCE_MANAGER_PLAN
initialization parameter to the plan with anALTER SYSTEM
statement when the current container is the PDB. - Modifying a PDB Resource Plan
You can use theDBMS_RESOURCE_MANAGER
package to modify a PDB resource plan in the same way you would modify the resource plan for a non-CDB. - Disabling a PDB Resource Plan
You disable a PDB resource plan by unsetting theRESOURCE_MANAGER_PLAN
initialization parameter in the PDB.
Parent topic: Using Oracle Resource Manager for PDBs
About PDB Resource Plans
A PDB resource plan determines how the resources for a specific PDB are allocated to consumer groups within this PDB.
A PDB resource plan is similar to a resource plan for a non-CDB. A PDB resource plan differs from a CDB resource plan, which determines the amount of resources allocated to each PDB.
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 the preceding 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.
This section contains the following topics:
- CDB Resource Plan Requirements When Creating PDB Resource Plans
When you create PDB resource plans, the CDB resource plan must meet certain requirements. - PDB Resource Plan: Example
A one-to-many relationship exists between CDB resource plans and PDB resource plans.
See Also:
-
Oracle Database Administrator’s Guide to learn more about resource plans
Parent topic: Managing PDB Resource Plans
CDB Resource Plan Requirements When Creating PDB Resource Plans
When you create PDB resource plans, the CDB resource plan must meet certain requirements.
Create directives for a CDB resource plan by using the DBMS_RESOURCE_MANAGER.CREATE_CDB_PLAN_DIRECTIVE
procedure. 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.
The following table describes the requirements for the CDB resource plan and the results when the requirements are not met. 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 21-9 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:
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 |
Parallel execution servers |
One of the following requirements must be met:
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 |
Parent topic: About PDB Resource Plans
PDB Resource Plan: Example
A one-to-many relationship exists between CDB resource plans and PDB resource plans.
The following figure shows an example of a CDB resource plan and a PDB resource plan.
Figure 21-4 A CDB Resource Plan and a PDB Resource Plan
Description of "Figure 21-4 A CDB Resource Plan and a PDB Resource Plan"
The preceding figure 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.
Parent topic: About PDB Resource Plans
Creating a PDB Resource Plan
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.
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.
The following is a summary of the steps required to create a PDB resource plan:
- In SQL*Plus, ensure that the current container is a PDB.
- Create a pending area using the
CREATE_PENDING_AREA
procedure. - Create, modify, or delete consumer groups using the
CREATE_CONSUMER_GROUP
procedure. - Map sessions to consumer groups using the
SET_CONSUMER_GROUP_MAPPING
procedure. - Create the PDB resource plan using the
CREATE_PLAN
procedure. - Create PDB resource plan directives using the
CREATE_PLAN_DIRECTIVE
procedure. - Validate the pending area using the
VALIDATE_PENDING_AREA
procedure. - 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 Oracle Database Administrator’s Guide 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 Oracle Database Administrator’s Guide for information about creating a simple resource plan.
Note:
Some restrictions apply to PDB resource plans. See "About PDB Resource Plans" for information.
Parent topic: Managing PDB Resource Plans
Enabling a PDB Resource Plan
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.
Prerequisites
Before enabling a PDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To enable a PDB resource plan:
-
In SQL*Plus, ensure that the current container is a PDB.
-
Use an
ALTER SYSTEM
statement to set theRESOURCE_MANAGER_PLAN
initialization parameter to the PDB resource plan.
You can also schedule a PDB resource plan change with Oracle Scheduler.
Example 21-5 Enabling a PDB Resource Plan
The following example sets the PDB resource plan to salespdb_plan
.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'salespdb_plan';
See Also:
-
Oracle Database Administrator’s Guide to learn how to schedule a PDB resource plan change with Oracle Scheduler
Parent topic: Managing PDB Resource Plans
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.
Prerequisites
Before modifying a PDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To modify a PDB resource plan:
-
In SQL*Plus, ensure that the current container is a PDB.
-
Create a pending area:
exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
-
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.
-
-
Validate the pending area:
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
-
Submit the pending area:
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
See Also:
-
Oracle Database Administrator’s Guide for instructions about completing the consumer group tasks
Parent topic: Managing PDB Resource Plans
Disabling a PDB Resource Plan
You disable a PDB resource plan by unsetting the RESOURCE_MANAGER_PLAN
initialization parameter in the PDB.
Prerequisites
Before disabling a PDB resource plan, complete the prerequisites described in "Overview of Oracle Resource Manager in a Multitenant Environment".
To disable a PDB resource plan:
-
In SQL*Plus, ensure that the current container is a PDB.
-
Use an
ALTER SYSTEM
statement to unset theRESOURCE_MANAGER_PLAN
initialization parameter for the PDB.
Example 21-6 Disabling a PDB Resource Plan
The following example disables the PDB resource plan.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '';
See Also:
Parent topic: Managing PDB Resource Plans
Monitoring PDBs Managed by Oracle Database Resource Manager
A set of dynamic performance views enables you to monitor the results of your Oracle Database Resource Manager settings for PDBs.
This section contains the following topics:
- About Resource Manager Views for PDBs
You can monitor the results of your Oracle Database Resource Manager settings for PDBs using views. - Monitoring CPU Usage for PDBs
TheV$RSRCPDBMETRIC
view enables you to track CPU metrics in milliseconds, in terms of number of sessions, or in terms of utilization for the past one minute. - Monitoring Parallel Execution for PDBs
TheV$RSRCPDBMETRIC
view enables you to track parallel statements and parallel server use for PDBs. - Monitoring the I/O Generated by PDBs
TheV$RSRCPDBMETRIC
view enables you to track the amount of I/O generated by PDBs. - Monitoring Memory Usage for PDBs
TheV$RSRCPDBMETRIC
view enables you to track the amount memory used by PDBs.
Parent topic: Using Oracle Resource Manager for PDBs
About Resource Manager Views for PDBs
You can monitor the results of your Oracle Database Resource Manager settings for PDBs using views.
The following views are available:
-
V$RSRCPDBMETRIC
The
V$RSRCPDBMETRIC
view provides current statistics on resource consumption for PDBs, including CPU usage, parallel execution, I/O generated, and memory usage. -
V$RSRCPDBMETRIC_HISTORY
The columns in the
V$RSRCPDBMETRIC_HISTORY
view are the same as the columns in theV$RSRCPDBMETRIC
view. The only difference between these views is that theV$RSRCPDBMETRIC
view contains metrics for the past one minute only, whereas theV$RSRCPDBMETRIC_HISTORY
view contains metrics for the last 60 minutes. -
V$RSRC_PDB
The
V$RSRC_PDB
view provides cumulative statistics. The statistics are accumulated since the time that the CDB resource plan was set. -
DBA_HIST_RSRC_PDB_METRIC
This view contains the historical statistics of
V$RSRCPDBMETRIC_HISTORY
, taken using Automatic Workload Repository (AWR) snapshots.
Note:
The V$RSRCPDBMETRIC
and V$RSRCPDBMETRIC_HISTORY
views record statistics for resources that are not currently being managed by Resource Manager when the STATISTICS_LEVEL
initialization parameter is set to ALL
or TYPICAL
.
See Also:
-
Oracle Database SQL Tuning Guide for more information about real-time SQL monitoring
-
Oracle Database Reference to learn about
V$RSRCPDBMETRIC
,V$RSRCPDBMETRIC_HISTORY
,V$RSRC_PDB
, andDBA_HIST_RSRC_PDB_METRIC
Monitoring CPU Usage for PDBs
The V$RSRCPDBMETRIC
view enables you to track CPU metrics in milliseconds, in terms of number of sessions, or in terms of utilization for the past one minute.
The view provides real-time metrics for each PDB and is very useful in scenarios where you are running workloads and want to continuously monitor CPU resource utilization.
The active CDB resource plan manages CPU usage for a PDB. Use this view to compare the maximum and average CPU utilization for PDBs with other PDB settings such as the following:
-
CPU time used
-
Time waiting for CPU
-
Average number of sessions that are consuming CPU
-
Number of sessions that are waiting for CPU allocation
For example, you can view the amount of CPU resources a PDB used and how long it waited for resource allocation. Alternatively, you can view how many sessions from each PDB are executed against the total number of active sessions.
Tracking CPU Consumption in Terms of CPU Utilization for PDBs
To track CPU consumption in terms of CPU utilization, query the CPU_UTILIZATION_LIMIT
and AVG_CPU_UTILIZATION
columns. AVG_CPU_UTILIZATION
lists the average percentage of the server's CPU that is consumed by a PDB. CPU_UTILIZATION_LIMIT
represents the maximum percentage of the server's CPU that a PDB can use. This limit is set using the UTILIZATION_LIMIT
directive attribute.
The following query displays this information by showing the container ID (CON_ID
) and name of each PDB:
COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID,
p.PDB_NAME,
r.CPU_UTILIZATION_LIMIT,
r.AVG_CPU_UTILIZATION
FROM V$RSRCPDBMETRIC r,
CDB_PDBS p
WHERE r.CON_ID = p.CON_ID;
Tracking CPU Consumption and Throttling for PDBs
Use the CPU_CONSUMED_TIME
and CPU_TIME_WAIT
columns to track CPU consumption and throttling in milliseconds for each PDB. The column NUM_CPUS
represents the number of CPUs that Resource Manager is managing.
The following query displays this information by showing the container ID (CON_ID
) and name of each PDB:
COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID,
p.PDB_NAME,
r.CPU_CONSUMED_TIME,
r.CPU_WAIT_TIME,
r.NUM_CPUS
FROM V$RSRCPDBMETRIC r,
CDB_PDBS p
WHERE r.CON_ID = p.CON_ID;
Tracking CPU Consumption and Throttling in Terms of Number of Sessions for PDBs
To track the CPU consumption and throttling in terms of number of sessions, use the RUNNING_SESSIONS_LIMIT
, AVG_RUNNING_SESSIONS
, and AVG_WAITING_SESSIONS
columns. RUNNING_SESSIONS_LIMIT
lists the maximum number of sessions from a particular PDB that can be running at any time. This limit is defined by the UTILIZATION_LIMIT
directive attribute that you set for the PDB. AVG_RUNNING_SESSIONS
lists the average number of sessions that are consuming CPU, and AVG_WAITING_SESSIONS
lists the average number of sessions that are waiting for CPU.
The following query displays this information by showing the container ID (CON_ID
) and name of each PDB:
COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID,
p.PDB_NAME,
r.RUNNING_SESSIONS_LIMIT,
r.AVG_RUNNING_SESSIONS,
r.AVG_WAITING_SESSIONS
FROM V$RSRCPDBMETRIC r,
CDB_PDBS p
WHERE r.CON_ID = p.CON_ID;
Monitoring Parallel Execution for PDBs
The V$RSRCPDBMETRIC
view enables you to track parallel statements and parallel server use for PDBs.
Parallel execution servers for a PDB are managed with the active CDB resource plan of the PDB's CDB. To track parallel statements and parallel server use for PDBs, use the AVG_ACTIVE_PARALLEL_STMTS
, AVG_QUEUED_PARALLEL_STMTS
, AVG_ACTIVE_PARALLEL_SERVERS
, AVG_QUEUED_PARALLEL_SERVERS
, and PARALLEL_SERVERS_LIMIT
columns.
AVG_ACTIVE_PARALLEL_STMTS
and AVG_ACTIVE_PARALLEL_SERVERS
list the average number of parallel statements running and the average number of parallel servers used by the parallel statements. AVG_QUEUED_PARALLEL_STMTS
and AVG_QUEUED_PARALLEL_SERVERS
list the average number of parallel statements queued and average number of parallel servers that were requested by queued parallel statements. PARALLEL_SERVERS_LIMIT
lists the number of parallel servers allowed to be used by the PDB.
The following query displays this information by showing the container ID (CON_ID
) and name of each PDB:
COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.AVG_ACTIVE_PARALLEL_STMTS, r.AVG_QUEUED_PARALLEL_STMTS,
r.AVG_ACTIVE_PARALLEL_SERVERS, r.AVG_QUEUED_PARALLEL_SERVERS, r.PARALLEL_SERVERS_LIMIT
FROM V$RSRCPDBMETRIC r, CDB_PDBS p
WHERE r.CON_ID = p.CON_ID;
Monitoring the I/O Generated by PDBs
The V$RSRCPDBMETRIC
view enables you to track the amount of I/O generated by PDBs.
I/O is limited for a PDB by setting the MAX_IOPS
initialization parameter or the MAX_MBPS
initialization parameter in the PDB. Use this view to compare the I/O generated by PDBs in terms of the number of operations each second and the number of megabytes each second.
Tracking the Number of I/O Operations Generated Each Second by PDBs
To track the I/O operations generated each second by PDBs during the previous minute, use the IOPS
column.
The following query displays this information by showing the container ID (CON_ID
) and name of each PDB:
COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.IOPS
FROM V$RSRCPDBMETRIC r, CDB_PDBS p
WHERE r.CON_ID = p.CON_ID;
Tracking the Number Megabytes Generated for I/O Operations Each Second by PDBs
To track number of megabytes generated for I/O operations each second by PDBs during the previous minute, use the IOMBPS
column.
The following query displays this information by showing the container ID (CON_ID
) and name of each PDB:
COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.IOMBPS
FROM V$RSRCPDBMETRIC r, CDB_PDBS p
WHERE r.CON_ID = p.CON_ID;
Monitoring Memory Usage for PDBs
The V$RSRCPDBMETRIC
view enables you to track the amount memory used by PDBs.
Use this view to track the amount of SGA, PGA, buffer cache, and shared pool memory currently used by PDBs.
To track the current memory usage, in bytes, for specific PDBs, use the SGA_BYTES
, PGA_BYTES
, BUFFER_CACHE_BYTES
, and SHARED_POOL_BYTES
columns.
The following query displays this information by showing the container ID (CON_ID
) and name of each PDB:
COLUMN PDB_NAME FORMAT A10
SELECT r.CON_ID, p.PDB_NAME, r.SGA_BYTES, r.PGA_BYTES, r.BUFFER_CACHE_BYTES, r.SHARED_POOL_BYTES
FROM V$RSRCPDBMETRIC r, CDB_PDBS p
WHERE r.CON_ID = p.CON_ID;