Ora-04031: unable to allocate nnnnn bytes of shared memory

[nagarjuna]

Originally posted by SANJAY_G
As Pando said, your SORT_AREA_SIZE is too big. If there are 5 sessions doing sorts concurrently, 1 GB of memory will be taken up by this 5 sessions.

Yep. Change the following parameters to lower values.

SORT_AREA_SIZE
SORT_AREA_RETAINED_SIZE

This should help you I guess.

[jmodic]

Originally posted by SANJAY_G
As Pando said, your SORT_AREA_SIZE is too big. If there are 5 sessions doing sorts concurrently, 1 GB of memory will be taken up by this 5 sessions.

Hey, don't scare him too much (or at least tell him the whole story)! If there are 5 sessions doing sorts concurently, then they will allocate exactly that amount of sort memory that is needed for sorting, with 200Mb being the upper limit for each sort. So if those 5 sessions need 2 Mb of sort memory each, they will consume 10 Mb of memory, not 1 GTB. Only if each of them is doing a real massive sort operation requiring 200Mb of sort space they will need 1 Gb (which is highly unlikely, I guess).

[tamilselvan]

It seems to be that your application is sending static SQL statements that causes the shared pool is quickly filled up. And finally it is fragmented. 400 MB for the shared pool is OK. Increase the size of SHARED_POOL_RESERVED_SIZE if you have free memort. And pin some of the large PKGs.

Or Use CURSOR_SHARING = FORCE in the init.ora file. Oracle will start change any static SQL into bind variable. This is a temporary solution. Only the application should be changed to use BIND variables so that SQL statements can be reused.

One more point: You can reduce the SORT_AREA_SIZE to 32MB. Various tests (I did) proved me that 32 MB is the right value for the SORT_AREA_SIZE parameter. Beyond that it is a waste and Oracle does more number of merges during the sort.

[Shestakov]

I guess that u have problem with coordination between
different part of a memory.

U can have up to 20 shared session and ??? active sessions.
if each 20 shared session will use up to 32MB of sort area
then totally it will be use up to 640MB memory in PGA(shared servers), and Oracle
don't free this memory after sorting.
Plus each of active sessions will use up to 2-4MB (application depend) addition
memory in shared pool and some memory in large pool.

My expirense say to me :
for 100 shared servers enought to have up to 64MB-96MB in a large pool
for most types of applications.

As most gays sugested i would be prefer memory configuration like this:

SHARED_POOL_SIZE = 550 MB
SHARED_POOL_RESERVED_SIZE = 50 MB
LARGE_POOL_SIZE = 96 MB
SORT_AREA_SIZE = 32 MB
SORT_AREA_RETAINED_SIZE = 16 MB

[DaPi]

Originally posted by Shestakov
As most gays . . .

I don't think you meant that!

[marist89]

Originally posted by DaPi
I don't think you meant that!

Appearantly the straight DBA's can go with a bigger LARGE_POOL_SIZE (if you know what I mean...)

[Shestakov]

Originally posted by DaPi
I don't think you meant that!

OK
let say "as i mean ..."

[krana]

To All the Gurus who have provided valuable advice:

HATS OFF TO ALL OF U AND THANX A LOT
I am OVERWHELMED at the response.

I wanted to check the impact of change in every parameter so
I did the following in the order specified
Increase large_pool_size to 300 M (Error Kept Coming)
Decrease sort_area_size to 150 M
Decrease sort_area_retained_size to 75 M (Sorts remained in memory)
Increase Shared_Pool size to 500 M

Haven't got the problem since 21st March and hope it stays
away. I am now setting up statspack to get in on the various
ratios and figures.

I also am of the view that sort_area_size can be reduced furthur
as 100 % sorts are taking place in memory.

I believe statspack reports will help me in arriving at better values
yet.

Thanx a lot again

Best Regards,

Kashif

[nagarjuna]

Krana,
Still your sort_area_size is too big. Have it smaller size.

For others,
increasing the shared_pool_size too much will result in shared pool fragmentation, thus ora-04031