SGA_MIN_SIZE in Oracle Database 12.2

I did summarize all new init.ora/spfile parameters in Oracle Database 12.2.0.1 a while ago:

And one caught Roy’s and my attention: SGA_MIN_SIZE.

When we present this new parameter as part of the Multitenant slides in our workshops, some people ask themselves:
“What happens if I oversubscribe such a parameter?” For example, if SGA_TARGET=4G and I have 10 PDBs and define in each of them SGA_MIN_SIZE=500M. Will the 9th and 10th PDB not startup anymore? Or will the 7th PDB already in trouble as the CDB$ROOT will need some SGA portion as well?

None of this is true.

If the sum of SGA_MIN_SIZE across all PDBs is larger than 50% of the CDB’s setting for SGA_TARGET, then SGA_MIN_SIZE will automatically scaled down to meet the criteria of not adding up across all PDBs to more than 50% of SGA_TARGET of the root.

If you scale down SGA_MIN_SIZE too small for a given PDB, you may see an error warning.

The documentation says:

• You must set the SGA_MIN_SIZE value to a value that meets these requirements:
  • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET in the PDB
  • In a PDB, to a value that is less than or equal to 50% of the value of SGA_TARGET at the CDB level
  • Across all the PDBs in a CDB, the sum of SGA_MIN_SIZE values must be less than or equal to 50% of the SGA_TARGET value at the CDB level.

  When you set SGA_MIN_SIZE in a PDB to a value that does not meet these requirements, you receive an error. If these requirements are violated after the PDB’s parameter is set (for example, if the SGA_MIN_SIZE value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements.

As you see, you won’t receive always an error. But in such a case where my SGA_TARGET in the CDB$ROOT is only 1.5GB, I will get the following error when I try to massively oversubscribe SGA_MIN_SIZE in a PDB:

SQL> alter session set container=pdb1;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=800M;

Error starting at line : 1 in command -
alter system set sga_min_size=800M
Error report -
ORA-32017: failure in updating SPFILE
ORA-56746: invalid value 838860800 for parameter sga_min_size; must be smaller
than 50% of parameter sga_target
32017. 00000 -  "failure in updating SPFILE"
*Cause:    A failure occured while updating the SPFILE.
*Action:   See associated errors.

SQL> alter system set sga_min_size=500M;
System SET altered.

First learning: SGA_MIN_SIZE gets treated relatively to the SGA_TARGET setting in the PDB.

Now lets try the same exercise in my second PDB:

SQL> alter session set container=pdb2;
Session altered.

SQL> alter system set sga_target=1G;
System SET altered.

SQL> alter system set sga_min_size=500M;
System SET altered.

Hm … interesting … let’s check in the CDB$ROOT:

SQL> alter session set container=cdb$root;
Session altered.

SQL> show sga
Total System Global Area   1577058304 bytes      
Fixed Size                    8793208 bytes      
Variable Size               385876872 bytes      
Database Buffers           1174405120 bytes      
Redo Buffers                  7983104 bytes    
  
SQL> show parameter sga_
NAME                         TYPE        VALUE   
---------------------------- ----------- ------- 
sga_max_size                 big integer 1504M   
sga_min_size                 big integer 0       
sga_target                   big integer 1504M   

It becomes a bit more clear that the values get adjusted internally (secretly) when you compare the values across PDBs:

SQL> select con_id, name, value from v$system_parameter where name like 'sga%' order by con_id;

    CON_ID NAME           VALUE               
---------- -------------- --------------------
         0 sga_min_size   0                   
         0 sga_max_size   1577058304          
         0 sga_target     1577058304          
         2 sga_target     0                   
         3 sga_target     1073741824          
         3 sga_min_size   524288000           
         4 sga_target     1073741824          
         4 sga_min_size   524288000           

8 rows selected. 

So finally, let’s monitor the actual SGA spread and consumption per PDB (thanks to Tim Hall for his queries):

SET LINESIZE 150
COLUMN pdb_name FORMAT A10

SQL> SELECT r.con_id,
            c.pdb_name,
            round(r.sga_bytes/(1024*1024),1) "SGA in MB",
            round(r.buffer_cache_bytes/(1024*1024),1) "BCh in MB",
            round(r.shared_pool_bytes/(1024*1024),1) "ShP in MB"
     FROM   v$rsrcpdbmetric r,
            cdb_pdbs c
     WHERE  r.con_id = c.con_id
     ORDER BY r.con_id;

    CON_ID PDB_NAME    SGA in MB  BCh in MB  ShP in MB
---------- ---------- ---------- ---------- ----------
         3 PDB2             37.3       25.2       12.1
         4 PDB1             38.9       25.5       13.4

–Mike