Ora-04031: unable to allocate nnnnn bytes of shared memory - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

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

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

  2. #12
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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.

  4. #14
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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

  5. #15
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by Shestakov
    As most gays . . .
    I don't think you meant that!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #16
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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...)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #17
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by DaPi
    I don't think you meant that!
    OK
    let say "as i mean ..."

  8. #18
    Join Date
    Jan 2003
    Posts
    21

    Thumbs up Thanx a lot everyone

    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

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

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