I decided to do a test case regarding this new parameter to have a better understanding and I could see some interesting results. So check it out and make your own conclusions.
The sga_min_size is set at PDB level and it guarantees the specified SGA size for the PDB.
Before start let’s check what Oracle documentation says regarding this parameter.
To set sga_min_size to a value we must meet there 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.
Otherwise we have:
– 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.
The test case consists to set sga_target to the same value (at CDB and PDB level) as also set sga_min_size to the same value as sga_target for both CBD and PDB level and see what happens.
Test case using (OEL 7.2 / Oracle 12.2.0.1.0 / Enterprise Edition / ASM)
1) CDB sga_target = 2000M and PDB01e sga_target=1000M
2) sga_min_size for PDB1e = 1000M
3) Changed CDB sga_target = 2000M to 1000M
4) Close and open PDB01e
5) What happen at this point?
1) CDB level
SQL> conn / as sysdba Connected. cdb02 >select con_id,instance_name from v$instance; CON_ID INSTANCE_NAME ---------- --------------- 0 cdb02 SQL> alter system set sga_target=2000M scope=both; System altered. |
2) PDB level
SQL> alter session set container=pdb01e; Session altered. SQL> show con_id; CON_ID ------------------------------ 3 SQL> show parameter sga_min_size NAME TYPE VALUE ------------------------------------ ----------- ----------------------------- sga_min_size big integer 0 |
3) Tried to change sga_min_size to 2000M at PDB level. This will not meet Oracle documentation and should raise an error (this is the expected behavior).
alter system set sga_target=2000M scope=both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-56747: invalid value 2097152000 for parameter sga_target; must be smaller than parameter sga_target of the root container |
4) Change sga_min_size to 1000M at PDB level (50%) of sga_target at CDB level.
SQL> alter system set sga_min_size=1000M scope=both; System altered. |
5) At CDB level set sga_target to 1000M
SQL> alter system set sga_target=1000M scope=both; System altered. |
6) At PDB level set sga_target to 1000M. This will not meet Oracle documentation and should raise an error (this is the expected behavior).
SQL> alter system set sga_target=1000M scope=both; alter system set sga_target=1000M scope=both * ERROR at line 1: ORA-02097: parameter cannot be modified because specified value is invalid ORA-56750: invalid value 1048576000 for parameter sga_target; must be larger than 200% of parameter sga_min_size |
7) At PDB level set sga_target to 1000M but scope only to spfile
SQL> alter system set sga_target=1000M scope=spfile; System altered. |
8) Close and open the PDB
SQL> alter pluggable database pdb01e close immediate; Pluggable database altered. SQL> alter pluggable database pdb01e open read write; Pluggable database altered. |
9) Let’s check the alert log file for messages (I just kept the import messages)
Pluggable database PDB01E closed PDB01E(3):Completed: alter pluggable database pdb01e close immediate 2017-07-27T12:00:21.197570+01:00 PDB01E(3):alter pluggable database pdb01e open read write Pluggable database PDB01E opened read write PDB01E(3):Completed: alter pluggable database pdb01e open read write 2017-07-27T12:00:26.872479+01:00 PDB01E(3):PDB01E(3):reducing SGA_MIN_SIZE from 1000MB to 500MB because it exceeds 50% of SGA_TARGET |
So here Oracle is checking the meets and logging a message saying that sga_min_size will be reduced to 500M (50% of sga_target that was set before).
10) At PDB level let’s check the value of sga_target and sga_min_size (Should appear 500M as informed in alertlog)
SQL> alter session set container=pdb01e;
Session altered.
SQL> show con_id
CON_ID
------------------------------
3
SQL> show parameter sga_min_size NAME TYPE VALUE ------------------------------------ ----------- -------------------------- sga_min_size big integer 1000M SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- -------------------------- sga_target big integer 1000M |
11) At CDB level let’s check sga_target value
SQL> conn / as sysdba Connected. cdb02 >select con_id,instance_name from v$instance; CON_ID INSTANCE_NAME ---------- --------------- 0 cdb02 SQL> show parameter sga_target NAME TYPE VALUE ------------------------------------ ----------- -------------------------- sga_target big integer 1000M |
12) At CDB level let’s access the value in v$system_parameter
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 2097152000 0 sga_target 1048576000 2 sga_target 0 3 sga_target 1048576000 3 sga_min_size 1048576000 6 rows selected. |
Now we supposed to have at PDB level sga_min_size 500M according to the alertlog but inside the database it appears 1000M. So what value we should consider in this behavior? I’m not sure at all but in particular I think the value returned in the v$system_parameter should be the right one to be considered (1000M) but again this is just a guess.
Just in case I also did a shutdown at CDB level to check if Oracle would cleanup something and decrease the sga_min_size to 500M but it still remains 1000M.
I did this test case with one PDB up to three PDBs and in all cases I got the same behavior. If we are talking about 1 CDB with 200+ PDBs this could be quite tricky because lacks transparency. I don’t know if Oracle just decided to keep this way or if it will be something to be fixed it.
Leonardo Bissoli.
