Answer

Non-zero values for SHARED_POOL_SIZE and JAVA_POOL_SIZE

I am getting an ORA-04031 error. I have a two-node 10g RAC cluster. If I gave both SHARED_POOL_SIZE and JAVA_POOL_SIZE values (thus overriding the defaults) how would this affect the running of the CLUSTER? Does a CLUSTER require these values to be 0?

Published: 04 Oct 2006
I am getting an ORA-04031 error: "unable to allocate 27996 bytes of shared memory("shared pool","unknown object","sga heap(1,0)","session parame")." I have a two-node 10g RAC cluster sitting on Windows Server 2003 Standard Edition x64, with 12GB of memory on each node.

From what I have read there are two solutions to the problem: (1) Tell the developers to use bind variables in their SQL statements, or (2) increase the shared pool size. However my understanding is not what it should be. My cluster database has the following memory parameter settings: 

  SGA_TARGET - 7808Mb
  shared_pool_reserve_size (node1) - 64Mb
  shared_pool_reserve_size (node2) - 63Mb
  SHARED_POOL_SIZE - 0
  JAVA_POOL_SIZE - 0
  LARGE_POOL_SIZE - 0
Why is the SHARED_POOL_SIZE 0? I thought that would only be 0 if SGHA_TARGET is not set (it is set to 7808Mb).

Secondly, the "session parme" phase of the error message implies a problem with the JAVA_POOL_SIZE. This also relates to the SGA_TARGET. If I gave both SHARED_POOL_SIZE and JAVA_POOL_SIZE values (thus overriding the defaults) how would this affect the running of the CLUSTER? Does a CLUSTER require these values to be 0? Thanks in advance.

To answer your direct questions first:

1. Why is the SHARED_POOL_SIZE 0? I thought that would only be 0 if SGA_TARGET is not set (it is set to 7808Mb)

The opposite is true. A value of 0 for SHARED_POOL_SIZE is a valid value ONLY if Automatic Shared Memory Management (ASMM) is activated by setting the SGA_TARGET.

2. If I gave both SHARED_POOL_SIZE and JAVA_POOL_SIZE values (thus overriding the defaults) how would this affect the running of the CLUSTER. Does a CLUSTER require these values to be 0?

When setting these parameters to a non-zero value in conjunction with SGA_TARGET, you are specifying the MINIMUMS for allocation by the ASMM facility. Non-zero values are certainly allowed and the cluster would perform as required.

Keep in mind that the SGA_TARGET specifies the TOTAL SGA and Oracle has to include in this allocated memory enough resources to accommodate your manually configured SGA parameters as well. Things like LOG_BUFFER, DB_KEEP_CACHE_SIZE, etc. still have to be manually set and they will be subtracted from your SGA_TARGET size for an actual amount of memory for ASMM to use.

Also, 10g creates a trace file in the, I believe, user_dump directory whenever an ORA-04031 occurs. If it is not there it is in the background_dump directory. This trace file should help you getter a better handle on what may be causing this, be it too many SQL statements without bind variables, too much memory dedicated to other SGA areas preventing sufficient allocation of shared_pool, etc.

It would also help to know what the nature of the application is, how often you see this, what else may be running on your server and what the values of your other memory parameters are to better diagnosis your problem.

I hope this helps, and good luck!
Bill

Dig Deeper on Oracle database administration