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 theSGA_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 theSGA_MIN_SIZE
value is changed at the CDB level, Oracle will adjust the PDB’s value to meet these requirements. - In a PDB, to a value that is less than or equal to 50% of the value of
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