DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

  1. #1
    Join Date
    Jan 2003
    Posts
    21

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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

  3. #3
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122
    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!
    ====================================================

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    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

  5. #5
    Join Date
    Nov 1999
    Location
    Kuwait
    Posts
    122
    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!
    ====================================================

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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

  8. #8
    Join Date
    Jan 2003
    Posts
    21
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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

  10. #10
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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
  •  


Click Here to Expand Forum to Full Width