ORA-04031: unable to allocate 4096 bytes of shared memory
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ORA-04031: unable to allocate 4096 bytes of shared memory

Hybrid View

  1. #1
    Join Date
    Dec 2001
    Location
    Duluth, Georgia, USA
    Posts
    99

    Question

    Hi All,

    We have Oracle8.1.7 Release 3 installed on Sun Solaris 64-bit machine with Solaris 8. We constantly have the

    'ORA-04031: unable to allocate 4096 bytes of shared memory'

    error message. In the init.ora file, we have set 'shared_pool_size = 10485760'. I don't think it is an application problem. Is it because some bugs in Oracle? Should we apply some patchset or is there a workaround? Your help is greatly appreciated.

    Jiong
    ---------------------------
    OCP (DBA 8, 8i, 9i, AD, iAD)
    SCSA, SCJP, SCWCD
    ---------------------------

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    10M shared pool? I would bump this up to at least 32M, preferably 64M if you have the Physical RAM.

    ORA 4031 errors usually result from shared pool fragmentation. There were some bugs in 8.1.7.1 in this area, but you probably just don't have enough for what you are trying to do. I would also look to make sure you are using bind variables as much as possible.
    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."

  3. #3
    Join Date
    Dec 2001
    Location
    Duluth, Georgia, USA
    Posts
    99
    Hi Jeff,

    Thank you for your suggestion. We have 2GB RAM. How big do you think we can put for shared_pool_size? Is 64, 128, or even 256 MB fine?

    Thanks,
    Jiong
    ---------------------------
    OCP (DBA 8, 8i, 9i, AD, iAD)
    SCSA, SCJP, SCWCD
    ---------------------------

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    From 64M to 128M for most application enough.

  5. #5
    Join Date
    May 2002
    Location
    England
    Posts
    78

    Try this...

    I have had this issue with two 8.1.7.3.0 databases. There is a patchset you can apply but there is also a workaround.

    Add the following to your init.ora:

    event="4031 trace name errorstack level 3"

    _db_handles_cached = 0

    works a treat.

    John

  6. #6
    Join Date
    Apr 2002
    Posts
    9
    Your shared pool is quite small. You can run the following script every 15 min during a day to estimate how much memory you need for the share pool size:
    ---------
    set numwidth 15
    column shared_pool_size format 999,999,999
    column sum_obj_size format 999,999,999
    column sum_sql_size format 999,999,999
    column sum_user_size format 999,999,999
    column min_shared_pool format 999,999,999
    select to_number(value) shared_pool_size,
    sum_obj_size,
    sum_sql_size,
    sum_user_size,
    (sum_obj_size + sum_sql_size+sum_user_size)* 1.3 min_shared_pool
    from (select sum(sharable_mem) sum_obj_size
    from v$db_object_cache),
    (select sum(sharable_mem) sum_sql_size
    from v$sqlarea),
    (select sum(250 * users_opening) sum_user_size
    from v$sqlarea), v$parameter
    where name = 'shared_pool_size';

    --------
    ORA-04031 also occurs if you use MTS mode and set the large pool size too small. USe the following script to estimate your large pool (avg(value) * max # of sessions)

    select a.name, avg(b.value)
    from v$statname a, v$sesstat b
    where a.statistic#= b.statistic# and a.name like '%uga%'
    group by a.name;
    --------


    david le

  7. #7
    Join Date
    Dec 2001
    Location
    Duluth, Georgia, USA
    Posts
    99
    Thanks for all you guys!

    I have applied the workaround and appreciate the input.

    Jiong
    ---------------------------
    OCP (DBA 8, 8i, 9i, AD, iAD)
    SCSA, SCJP, SCWCD
    ---------------------------

  8. #8
    Join Date
    Dec 2001
    Location
    Duluth, Georgia, USA
    Posts
    99
    David,

    I found your scripts very helpful. Thank you for sharing them with us.

    Jiong
    ---------------------------
    OCP (DBA 8, 8i, 9i, AD, iAD)
    SCSA, SCJP, SCWCD
    ---------------------------

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