Test case using SGA_MIN_SIZE in Oracle 12.2

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.
ORA-Stuff_Logo

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s