Performance Issues with Pluggable Databases (PDBs) - Set Parameter "TARGET_PDBS" (Versions 18.1.0 to 19.8.0)

Performance Issues with Pluggable Databases (PDBs) - Set Parameter "TARGET_PDBS" (Versions 18.1.0 to 19.8.0)

Environment:
==========

Oracle GI:                 Oracle 19c (19.7.0)
Oracle RDBMS:       Oracle 19c (19.8.0)
Container Database: Container Database (orcldb) with 

                                  four pluggable databases (pdb1, pdb2, pdb3 and pdb4)
Introduction:

==========
Starting with the introduction of certain Pluggable Database (PDB) enhancements, Oracle RAC sizes its internal data structures to accommodate hundreds of PDBs in the same Container Database (CDB) instance. 

Running smaller number of PDBs need to consider the following best practices in order to ensure good 
performance and availability.

Using the new default sizing for internal data structures, Oracle Real Application Clusters (RAC) 
CDBs where the number of actual PDB’s is lower than the TARGET_PDBS parameter setting can inadvertently  be subject to a negative performance impact due to the internal sizing for a much larger number of PDBs in the same CDB..

Set the initorcldb.ora parameter TARGET_PDBS to the number of PDBs that are planned to be running in the CDB. 

Please do not add seed (PDB$SEED) and root (CDB$ROOT) in this count.

If the current number of PDBs is 4, but the plan is to run 10 PDBs TARGET_PDBS should be set to 10 accordingly. 

The number does not have to be exact but as close as possible to the number of planned PDBs Target_PDBS=<#_PDBs>

This init.ora parameter can be set in the spfile and is best activated using a a rolling restart of the Oracle RAC instances.

Note: This parameter only needs to be set in Oracle Multintenant-enabled databases. It does not apply to Non-CDB environments, neither to Oracle Autonomous Database 

[oracle@rac1-oel77-122 ~]$ srvctl status database -d orcldb
Instance orcldb1 is running on node rac1-oel77-122
Instance orcldb2 is running on node rac2-oel77-123
[oracle@rac1-oel77-122 ~]$

[oracle@rac1-oel77-122 ~]$ sqlplus sys@orcldb as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 30 22:00:29 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
SQL>

SQL> show parameter target_pdb
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
target_pdbs                          integer     5
SQL>

SQL> alter system set target_pdbs=6 scope=spfile sid='*';
System altered.

SQL> alter system set target_pdbs=6 scope=both sid='*';
alter system set target_pdbs=6 scope=both sid='*'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
[oracle@rac1-oel77-122 ~]$

[oracle@rac1-oel77-122 ~]$ srvctl stop database -d orcldb
[oracle@rac1-oel77-122 ~]$

[oracle@rac1-oel77-122 ~]$ srvctl start database -d orcldb
[oracle@rac1-oel77-122 ~]$

[oracle@rac1-oel77-122 ~]$ srvctl status database -d orcldb
Instance orcldb1 is running on node rac1-oel77-122
Instance orcldb2 is running on node rac2-oel77-123
[oracle@rac1-oel77-122 ~]$

Note: Created new pluggable database "PDB4".

[oracle@rac1-oel77-122 ~]$ sqlplus sys@orcldb as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Aug 30 22:03:31 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           MOUNTED
         6 PDB4                           READ WRITE NO
SQL>

SQL> alter pluggable database pdb3 open read write instances=all;
Pluggable database altered.

SQL> alter pluggable database pdb3 save state instances=all;
Pluggable database altered.

SQL> alter pluggable database pdb4 save state instances=all;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
         5 PDB3                           READ WRITE NO
         6 PDB4                           READ WRITE NO
SQL>

SQL> show parameter target_pdbs
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
target_pdbs                          integer     6
SQL>

SQL> select instance_name,instance_number from gv$instance;
INSTANCE_NAME    INSTANCE_NUMBER
---------------- ---------------
orcldb1                        1
orcldb2                        2
SQL>

Please refer for more details: 
Performance Issues when using PDBs with Oracle RAC 19c and 18c (Doc ID 2644243.1)

Summary: Consider to set proper value for "TARGET_PDBS" in multi-tenant environments. Otherwise we can expect High "latch: gcs resource hash", "gcs drm freeze in enter server mode" potentially in combination with “"gcs drm freeze in enter server mode" or "latch:ges resource hash list" wait events in AWR reports are indicative of the sizing misalignment.