Share Memory issue : ORA-04031: unable to allocate 3936 bytes

[Former Member]

Hello Colleagues,

Please help me to fix the share memory frequency error occurance in my development system.

When ever we try to do the client copy the share memory problem is occuring

Database error text........: "ORA-04031: unable to allocate 3936 bytes of

shared memory ("shared pool","SELECT "TABNAME" , "DEVCLASS...","sga

heap(1,0)","kglsim object batch")"

Please find the current system config & memory size mentioned in the initsap.ora file and spfile

System Configuration

Intel(R) Xeon CPU

E5520 @2.27Ghz

6GB Ram

Paging file Size : 20400 MB

*.sga_max_size 10612736

*.sga_target 98907648

*.shared_pool_reserved_sizeH239738

*.shared_pool_sizeR8482304

*.db_block_size92

*.db_cache_sizeR8482304

SPFILE

*.sga_max_size 10612736

*.sga_target 98907648

*.shared_pool_reserved_sizeH239738

*.shared_pool_sizeR8482304

OS : Windows 2003 R2, Oracle 10g

Please any one guide us for tunning SGA and where changes need to be done

Best Regards,

Jai

[Former Member]

All you need to know is [830576 - Parameter recommendations for Oracle 10g|https://service.sap.com/sap/support/notes/830576]

In your case the shared_pool_size is to small, it should rather be something like 400mb.

Cheers Michael

[Former Member]

Hi

Thanks for your valuable input, I already had look at 830576 note may be you got confused with values mentioned in the above

Please find the current vaules

Paging file Size : 20400 MB

*.sga_max_size= 1610612736

*.sga_target= 1098907648

*.shared_pool_reserved_size= 48239738

*.shared_pool_size= 528482304

*.db_block_size= 8192

*.db_cache_size= 528482304

SPFILE

*.sga_max_size= 1610612736

*.sga_target= 1098907648

*.shared_pool_reserved_size= 48239738

*.shared_pool_size= 528482304

The Shared_pool Size is around 504Mb Please suggest the configuration sizing to fix the SHM problem

Best Regard,

Jai

[Former Member]

Hi,

Is this and x86 or x64 machine ?

Mark

[Former Member]

Hi Mark

Its X86 system

Regards,

Jai

[Former Member]

Hi,

Though 32bit Windows can handle upto 8GB with the PAE switch on in the boot.ini file

it is still only a 32bit machine which addresses 4GB.

What is your memory allocation when you start running into problems ?

Mark

[audunlea_hansen]

If possible, try to set sga_max_size and sga_target to 800MB or higher.

Often Oracle databases like to allocate around 380MB in shared_pool part of the sga. You want the buffer_pool part of sga as big as possible to cache most of the high frequent used data.

Regards

Audun

DBA