Oracle DBA – A lifelong learning experience

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 – 11.1.0.6 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 11.1.0.7

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 11.1.0.6 and 11.1.0.7

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 11.1.0.7 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 11.1.0.7 (recommended) or using one of the following workarounds:

Start the database having explicitly set:

_ksmg_granule_size=16777216

in the init.ora file.

or

Start the database with a reduced number of subpools in shared pool by explicitly setting:

_kghdsidx_count=2

 Hope this is useful heads up to anybody who has a similar issue

About these ads

2 Responses to “Problems with SGA being a multiple of 4Gb (and high cpu count)”

  1. [...] that error which came with link which I helped us and also confirmed from [...]

  2. Gopi said

    Thanks you saved us. I used your blog URL in my blog.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 184 other followers

%d bloggers like this: