Problems with SGA being a multiple of 4Gb (and high cpu count)
Posted by John Hallas on November 13, 2009
We had a server – HPUX IA64 – 220.127.116.11 where we increased the number of CPUs to 32 and upped memory to 96Gb. After some performance testing we tried to set the parameters as follows
alter system set sga_target=20G scope=spfile sid=’*';
alter system set sga_max_size=20G scope=spfile sid=’*';
alter system set shared_pool_size=800M scope=spfile sid=’*';
alter system set shared_pool_reserved_size=80M scope=spfile sid=’*';
alter system set pga_aggregate_target=4G scope=spfile sid=’*';
We couldn’t start the database because of lack of memory and we saw ORA-04030 errors
ORA-04030: out of process memory when trying to allocate 840 bytes (kgsp-heap,kglss)
However setting sga_target to 21G allowed the database to start.
We found a note on Metalink suggesting that SGA_TARGET did not like being set to multiples of 4G which seemed to be proved in the above example. However that is an easily disprovable statement as I am sure we all have databases with SGA_TARGET = 4,8,12G.
On further analysis and discussions with Oracle we came up with the following information. Firstly the issue is associated with a high number of CPUs and it is a bit difficult for us to test as we don’t any servers we can test on with 32 or more CPUs
Bug 6624011 – ORA-4030 / ORA-4031 on startup with > 4Gb (and large amount of CPU’s)
This is generic to all platforms and fixed in 18.104.22.168
current workaround is to set the SGA_TARGET to 19G.
Bug 8813366 – ORA-4031 ERRORS DUE TO EXCESSIVE GRANULE SIZE IN 11G
This is generic to all platforms and applicable to 22.214.171.124 and 126.96.36.199
It has the following symptons
– Memory_target is used and set to a high value (8GB for example).
– The shared pool is divided in subpools due to high number of CPUs.
– ORA-4031 trace file shows several subpools with no memory allocated.
Once we have upgraded Production to 188.8.131.52 another attempt to start up the DB with a multiple of 4G should be made.
However, the startup may fail with ORA-04031 unable to allocate 56 bytes of shared memory. This is documented in Metalink Note 831770.1 and fixed by applying patch 7441663 on top of 184.108.40.206 (recommended) or using one of the following workarounds:
Start the database having explicitly set:
in the init.ora file.
Start the database with a reduced number of subpools in shared pool by explicitly setting:
Hope this is useful heads up to anybody who has a similar issue