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

[krana]

Dear Gurus,

For the past few days we are getting the following
error especially when users open LOVs in forms.

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

I have recently joined as dba and to remove some
internal error codes installed 8.1.7.4.1 patchset
which has also taken care of persistent 100% CPU
utilization problem.

Now I am looking into the memory
parameters and need ur advice / help on configuring
the shared pool and also how to avoid this problem.

Currently I query the db for users utilizing most
memory and kill the sessions on top

Our current db platform is:
DB 8.1.7.4.1
OS Windows NT 4 (SP 6)

Memory Installed 2 GB
Availaible Memory 900 MB

SHARED_POOL_SIZE = 400 MB
SHARED_POOL_RESERVED_SIZE = 50 MB
LARGE_POOL_SIZE = 250 MB
SORT_AREA_SIZE = 200 MB
SORT_AREA_RETAINED_SIZE = 100 MB


Best Regards,

Kashif

[hrishy]

Hi

Killing the sessions will not help..are you getting this error frequently..investigate wheather your applications i using bind variables..You could try increasing the shared pool size parameter..

regards
Hrishy

[nabeel]

Cause: More shared memory is needed than was allocated in the shared pool.

Action: If the shared pool is out of memory, either use the DBMS_SHARED_ POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_ POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.

Try reducing the SORT_AREA_SIZE = 100MB SORT_AREA_RETAINED_SIZE = 50 MB and give 100MB to SHARED_POOL_SIZE and then try, hope you could come over the problem.

[hrishy]

Originally posted by nabeel
Cause: More shared memory is needed than was allocated in the shared pool.

Try reducing the SORT_AREA_SIZE = 100MB SORT_AREA_RETAINED_SIZE = 50 MB and give 100MB to SHARED_POOL_SIZE and then try, hope you could come over the problem.

how can reducing the shared pool size help..i think he needs to increase it..arent you contracdicting yourself here ?

regards
Hrishy

[nabeel]

I asked him to reduce the sort_area_size and give that 100M to shared_pool means increase the size of shared_pool by 100M.

Sorry if i have written it wrong but this is what i meant!

[pando]

sort_area_size is too big, in an OLTP 1MB is more than enough (and you have 200MB!)
even in a dwh 32 to 64 MB should be enough

[nagarjuna]

Ohhhhhhhhhh...

Too many answers... hummm


KRANA,

Tell me this,

whether the database is configured to be run in MTS?

Give me the size of your database and out put of "show sga"

[krana]

Hello All,

Thanx for the advice u guys.

I also looked at metalink and found out that the problem is
basically due to memory management of large pool which doesn't
use an LRU mechanism.

One way of solving is to increase large pool so did that by increasing from 250 to 300 M.

Will keep monitoring to check if it is enough.

One point to discuss is large_pool_min_alloc

I understand that it is no more required in 8i as large pool
is allocated in chunks of 64 K by default.

The biggest problem is not being able to dynamically alter
sga + large pool in 8i. We will be upgrading to 9i in about
a month but until then are stuck with having to reboot the system / bounce the database to apply init.ora changes.

Physical size (DB Files + Control files + Redo Logs) = 12 GB

Our database is running in MTS configuration with
following 10 dispatchers & 20 servers.

Output from sga is:

Total System Global Area 1095284764 bytes
Fixed Size 75804 bytes
Variable Size 767451136 bytes
Database Buffers 327680000 bytes
Redo Buffers 77824 bytes

Best Regards,

Kashif

[nagarjuna]

Originally posted by krana
Hello All,

Our database is running in MTS configuration with
following 10 dispatchers & 20 servers.



There you are ;)

It's your sort_area_size and hash_area_size using your large pool. You have to 2 options. Either to increase your large_pool_size or change back to dedicated server configuration. Whar are your sort area and hash area sizes? what is your max_roles_enabled? and what is the your db_files parameter set for?

[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.

[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