How to Control and Monitor the Memory Usage (Both SGA and PGA) Among the PDBs in Mutitenant Database

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2 BETA1 and later
Information in this document applies to any platform.

PURPOSE

This document explains the Memory Resource Management in a multi tenant database.

SCOPE

The scope of this bulletin is to discuss the various options available to manage the memory (both SGA and PGA) usage among the PDBs in a multitenant database. Basic database administration skill is a prerequisite.

DETAILS

Memory Management using Resource Manager

Oracle Database Resource Manager (the Resource Manager) can now manage memory usage between pluggable databases (PDBs) within a multitenant container database (CDB). This feature helps in maintaining the performance of all PDBs in a CDB by ensuring none of the PDBs grab more resources leading to a resource crunch on other PDBs.

Several initialization parameters can control the memory usage of a PDB. Oracle Database Resource Manager allows the sharing of memory among the PDBs in a flexible way.

In 12.2, Resource Manager allows to:

1. Limit the memory usage of a particular PDB
2. Specify the amount of memory guaranteed for a particular PDB
3. Specify the maximum amount of memory a particular PDB can use

along with other options such as:

• 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
• Use PDB performance profiles for different sets of PDB (Refer Note 2171135.1 for details)
• 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

Managing SGA for PDBs

The SGA requirement for the various PDBs in a container database will be different. If there is no mechanism to control the SGA usage, an active PDB can consume majority of the SGA space leading to a resource constraint to other PDBs, affecting their performance.

From 12cR2, we can control the maximum SGA that can be used by a PDB in the container database as well as the minimum SGA that need to be allocated for a PDB.

1. SGA_TARGET parameter can be used to limit the maximum SGA size for the PDB.

2. SGA_MIN_SIZE parameter can be used to specify the minimum SGA size for the PDB.

SGA_MIN_SIZE parameter ensures that the SGA for that PDB will never go below the specified value.

The guideline for setting SGA_MIN_SIZE (minimum guaranteed SGA) parameter is:

• It must be less than or equal to 50% of the setting for the SGA_TARGET in the CDB root.
• It must be less than or equal to 50% of the setting for the SGA_TARGET in the PDB.
• The sum of the SGA_MIN_SIZE settings for all PDBs must be less than or equal to 50% of the setting for the SGA_TARGET in the CDB root.

The best practice is to limit the sum of the SGA_MIN_SIZE values for all PDBs to 50% or less of the SGA size of the CDB.

Both SGA_TARGET and SGA_MIN_SIZE parameters can be set in the PDB level.

For more granular control, you can even set the parameters like DB_CACHE_SIZE (guaranteed Buffer Cache size for PDB) and SHARED_POOL_SIZE (guaranteed Shared Pool size for PDB) too.

All these parameters are required only if the SGA usage in the PDB need to be controlled. Its not mandatory to have them set.

Managing PGA for PDBs

To control the PGA usage by a PDB, the parameters PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT can be set in the PDB level.

The PGA_AGGREGATE_TARGET setting is a target. Therefore, Oracle Database tries to limit PGA memory usage to the target, but usage can exceed the setting at times. To specify a hard limit on PGA memory usage, use the PGA_AGGREGATE_LIMIT initialization parameter. Oracle Database ensures that the PGA size does not exceed this limit. If the database exceeds the limit, then the database aborts calls from sessions that have the highest untunable PGA memory allocations.

PGA_AGGREGATE_TARGET parameter sets the target aggregate PGA size for the PDB.

The guideline for setting PGA_AGGREGATE_TARGET is:

• It must be less than or equal to the PGA_AGGREGATE_TARGET value set at the CDB level.
• It must be less than or equal to 50% of the PGA_AGGREGATE_LIMIT initialization parameter value set at the CDB level.
• It must be less than or equal to 50% of the PGA_AGGREGATE_LIMIT value set in the PDB.

PGA_AGGREGATE_LIMIT sets the maximum PGA that the PDB can use at any time.

The guideline for setting PGA_AGGREGATE_LIMIT is:

• It must be less than or equal to the setting for the PGA_AGGREGATE_LIMIT in the CDB root.
• It must be greater than or equal to two times the setting for the PGA_AGGREGATE_TARGET in the PDB.

As mentioned earlier, both these parameters can be set in the PDB level.

Monitoring Memory Usage of PDBs

The following views to help you monitor the resource usage by PDBs:

• V$RSRCPDBMETRIC
• V$RSRCPDBMETRIC_HISTORY

The V$RSRCPDBMETRIC view provides current statistics on resource consumption for PDBs, including CPU usage, parallel execution, I/O generated, and memory usage. Historical statistics are available through the DBA_HIST_RSRC_PDB_METRIC view, which contains Automatic Workload Repository (AWR) snapshots.

• SGA_BYTES – PDB’s current SGA usage
• BUFFER_CACHE_BYTES – PDB’s current buffer cache usage
• SHARED_POOL_BYTES – PDB’s current shared pool usage
• PGA_BYTES – PDB’s current PGA usage