-
Ora-04031: unable to allocate nnnnn bytes of shared memory
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
-
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
-
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.
NK
====================================================
Stand up for your principles even if you stand alone!
====================================================
-
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
-
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!
NK
====================================================
Stand up for your principles even if you stand alone!
====================================================
-
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
-
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"
-nagarjuna
-
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
-
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?
-nagarjuna
-
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.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|