Configuring Shared Global Area (SGA) in a Multitenant Database

I have been working on a PeopleSoft Financials application that we have converted from a stand-alone database to be the only pluggable database (PDB) in an Oracle 19c container database (CDB).  We have been getting ORA-4031 (unable to allocate shared memory) errors in the PeopleSoft application.  

It has taken a while to solve and test, and I have to acknowledge quite a lot of advice from my friends.  

If you are wondering why you should be involved with your local Oracle user group, and regularly attend their meetings, this is an example: So you can ask people who have experience of different systems in different situations that you haven't encountered yet!

Documentation

I am going to look at 6 initialisation parameters that control the use of SGA.  The Oracle documentation, even in 21c, suggests they can mostly be set at CDB and PDB levels.  However, more recent Oracle guidance confirmed by my own experience suggests that is not a good idea.

• SGA_MAX_SIZE can only be set at CDB level.  It sets the size of the shared memory segment that is the SGA.  It cannot be changed during the life of the database instance.  
• Recommendation:
• It can be useful to set it higher than SGA_TARGET if you plan either to increase SGA_TARGET, or add PDBs to the CDB, without restarting the instances.
• SGA_TARGET "specifies the total size of all SGA components".  Use this parameter to control the memory usage of each PDB.  The setting at CDB must be at least the sum of the settings for each PDB.
• Recommendations:
• Use only this parameter at PDB level to manage the memory consumption of the PDB.
• In a CDB with only a single PDB, set SGA_TARGET to the same value at CDB and PDB levels.  
• Therefore, where there are multiple PDBs, SGA_TARGET at CDB level should be set to the sum of the setting for each PDB.  However, I haven't tested this yet.
• There is no recommendation to reserve SGA for use by the CDB only, nor in my experience is there any need so to do.
• SHARED_POOL_SIZE sets the minimum amount of shared memory reserved to the shared pool.  It can optionally be set in a PDB.  
• Recommendation: However, do not set SHARED_POOL_SIZE at PDB level.  It can be set at CDB level.
• DB_CACHE_SIZE sets the minimum amount of shared memory reserved to the buffer cache. It can optionally be set in a PDB.  
• Recommendation: However, do not set DB_CACHE_SIZE at PDB level.  It can be set at CDB level.
• SGA_MIN_SIZE has no effect at CDB level.  It can be set at PDB level at up to half of the manageable SGA
• Recommendation: However, do not set SGA_MIN_SIZE.
• INMEMORY_SIZE: If you are using in-memory query, this must be set at CDB level in order to reserve memory for the in-memory store.  The parameter defaults to 0, in which case in-memory query is not available.  The in-memory pool is not managed by Automatic Shared Memory Management (ASMM), but it does count toward the total SGA used in SGA_TARGET.
• Recommendation: Therefore it must also be set in the PDB where in-memory is being used, otherwise we found (contrary to the documentation) that the parameter defaults to 0, and in-memory query will be disabled in that PDB.

Oracle Notes

There are a lot of Oracle support notes on the subject SGA management in a multi-tenant database.  The older nodes talk about setting memory parameters in the PDB, and a later note and a bug advises only setting these parameters at CDB level, and not at all in the PDB.

• How to Control and Monitor the Memory Usage (Both SGA and PGA) Among the PDBs in Multitenant Database- 12.2 New Feature (Doc ID 2170772.1) – December 2018, updated July 2023
• This document discusses setting SGA_MIN_SIZE at PDB level.
• The diagrams tend to suggest that there is a requirement to reserve some memory for the CDB SGA, but it does not suggest how it might be done.  Presumably by making sure the sum of SGA_TARGET for each PDB are less than SGA_TARGET at CDB level. The documentation states that SGA_MIN_SIZE has no effect at CDB level,

• How To Modify memory parameters On a Pluggable Database (PDB) (Doc ID 2706020.1) – September 2020 - Updated March 2024
• This note discusses setting SHARED_POOL_SIZE, DB_CACHE_SIZE, and MIN_SGA_SIZE 

• How to Control the SGA Memory Usage Among the CDB/PDBs in Multitenant Database. (Doc ID 2712535.1) – September 2020, Updated April 2023
• This note discusses setting SHARED_POOL_SIZE, DB_CACHE_SIZE and SGA_MIN_SIZE at PDB level.

• ORA-04031 on Multitenant Database with Excessive Amounts of KGLH0 and / or SQLA Memory and Parameter SHARED_POOL_SIZE or SGA_MIN_SIZE Set at the PDB Level (Doc ID 2590172.1) – December 2022, Updated April 2023
• This one says “Remove the PDB-level SHARED_POOL_SIZE and/or SGA_MIN_SIZE initialization parameters. The only SGA memory sizing parameter that Oracle recommends setting at the PDB level is SGA_TARGET.”

• About memory configuration parameter on each PDBs (Doc ID 2655314.1) – November 2023
• “As a best practice, please do not to set SHARED_POOL_SIZE and DB_CACHE_SIZE on each PDBs and please manage automatically by setting SGA_TARGET.”
• "This best practice is confirmed by development in Bug 30692720"
• Bug 30692720 discusses how the parameters are validated.  Eg. "Sum(PDB sga size) > CDB sga size"
• Bug 34079542: "Unset sga_min_size parameter in PDB."

SGA Management with a Parse Intensive System (PeopleSoft).

PeopleSoft systems dynamically generate lots of non-shareable SQL code.  This leads to lots of parse and consumes more shared pool.  ASMM can respond by shrinking the buffer cache and growing the shared pool.  However, this can lead to more physical I/O and degrade performance and it is not beneficial for the database to cache dynamic SQL statements that are not going to be executed again.  Other parse-intensive systems can also exhibit this behaviour.

In PeopleSoft, I normally set DB_CACHE_SIZE and SHARED_POOL_SIZE to minimum values to stop ASMM shuffling too far in either direction.  With a large SGA, moving memory between these pools can become a performance problem in its own right.  

We removed SHARED_POOL_SIZE, DB_CACHE_SIZE and SGA_MIN_SIZE settings from the PDB.  The only SGA parameters set at PDB level are SGA_TARGET and INMEMORY_SIZE.  

SHARED_POOL_SIZE and DB_CACHE_SIZE are set as I usually would for PeopleSoft, but at CDB level to guarantee a minimum buffer cache size.  

This is straightforward when there is only one PDB in the CDB.   I have yet to see what happens when I have another active PDB with a non-PeopleSoft system and a different kind of workload that puts less stress on the shared pool and more on the buffer cache.

TL;DR

• Do not set any SGA parameter in a PDB other than SGA_TARGET and (if necessary) INMEMORY_SIZE.
• Do not set DB_CACHE_SIZE, SHARED_POOL_SIZE at PDB level.  They can be set at CDB level. 
• Do not set SGA_MIN_SIZE at either PDB or CDB level.