Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB

Below is some basic information on how database level parameters are managed between CD and PDB databases in Oracle multitenant architecture:

Changing parameters for CDB

Connect to CDB as sysdba

SQL> alter system set parameter_name=value;
SQL> alter system set parameter_name=value container=current;

Both of the commands will produce the same result

Changing parameters for CDB and all PDBs:

Connect to CDB as sysdba

SQL> alter system set parameter_name=value container=all;

“all” means parameter will get setup in cdb and all pdb.

Changing Parameters in PDB only:

IMPORTANT to remember: PDBs inherit parameter values from a CDB; unless they are overridden from the PDB level.

IMPORTANT to remember: Only the information about CDB is stored in the database SPFILE. PDB parameters are NOT stored in the DB SPFILE

Connect to CDB as sysdba and then switch to PDB:

SQL> alter session set container="<PDB_NAME>";
SQL> alter system set parameter_name=value scope=both sid='*';

VERY IMPORTANT: Only a subset of the initialization parameters can be modified locally in the PDB. These can be displayed using the following query.

set lines 132
col name format a40
col value format a40
select name, value from   v$system_parameter where  ispdb_modifiable = 'TRUE' order by name;

When you change PDB-specific initialization parameters in the PDB they are not stored in the SPFILE. Instead, they are saved in the PDB_SPFILE$ system table

NOTE: When you reset a parameter in PDB, it will take the default value as CDB value.

Also, you can use GV$PARAMETER, GV$SYSTEM_PARAMETER and GV$SPPARAMETER views, which all include a CON_ID column.
GV$SYSTEM_PARAMTER information is coming from the X$KSPPI and X$KSPPSV base tables.

CONTROLLING SGA BETWEEN DIFFERENT PDB

IMPORTANT to remember: If you are running a single PDB setup, there is no point using these settings as you want the PDB to use all the memory assigned to the instance.

In a CDB, the SGA requirements of different PDBs are different. If there is no SGA control mechanism, an active PDB may consume the main SGA space and cause other PDB resources to be strained, resulting in performance problems.

The SGA_TARGET parameter can be used to limit the maximum SGA usage of a PDB.
The SGA_TARGET parameter of the PDB must be less than or equal to the SGA_TARGET setting of the CDB.
The SGA_MIN_SIZE parameter is used to ensure that the SGA of the PDB will never be less than the set value.

For SGA_MIN_SIZE:

Must be less than or equal to 50% of the SGA_TARGET parameter of CDB
Must be less than or equal to 50% of the SGA_TARGET parameter of the PDB
The sum of the SGA_MIN_SIZE values of all PDBs must be less than or equal to 50% of the SGA_TARGET parameter of the CDB
Only when the SGA_TARGET of the CDB is set to a non-zero value, the SGA_TARGET and SGA_MIN_SIZE of the PDB will work.

CONTROLLING PGA BETWEEN DIFFERENT PDB

IMPORTANT to remember: If you are running a single PDB setup, there is no point using these settings as you want the PDB to use all the memory assigned to the instance.

In order to control the PGA usage of the PDB, the parameters PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT can be set at the PDB level.

The PGA_AGGREGATE_TARGET setting is a target. Therefore, the Oracle database will try to control the use of PGA not to exceed this value, but sometimes it still exceeds.

In order to specify a mandatory limit, you can use the PGA_AGGREGATE_LIMIT initialization parameter. The Oracle database will ensure that the use of PGA does not exceed this value. Once exceeded, the database will terminate the session with the largest untunable PGA memory allocation.

The PGA_AGGREGATE_TARGET parameter sets the cumulative target PGA size of the PDB.

The setting guide of PGA_AGGREGATE_TARGET parameter is as follows;

Must be less than or equal to the PGA_AGGREGATE_TARGET value of CDB.
Must be less than or equal to 50% of the CDB’s PGA_AGGREGATE_LIMIT value.
Must be less than or equal to 50% of the PDB’s PGA_AGGREGATE_LIMIT value.
PGA_AGGREGATE_LIMIT sets the maximum PGA size that can be used by the PDB at any time.

The setting guide of PGA_AGGREGATE_LIMIT parameter is as follows:

Must be less than or equal to the PGA_AGGREGATE_LIMIT value of CDB.
Must be greater than or equal to the PGA_AGGREGATE_TARGET value of the PDB.

Reference:

Initialization parameters in a Multitenant database – Facts and additional information (Doc ID 2101596.1)
Initialization parameters in a Multitenant database – FAQ and Examples (Doc ID 2101638.1)
Oracle 12c Multitenant: Frequently Asked Questions (Doc ID 1511619.1)
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/using-oracle-resource-manager-for-pdbs-with-sql-plus.html#GUID-A3459A8B-A36A-44D4-9FCD-75CA0E3D3252

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB

Related posts: