DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

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

  1. #1
    Join Date
    Aug 2000
    Posts
    462
    We're having a problem with intermittent denial of logons due to a severely fragmented shared pool.
    AIX 4.3.2
    Oracle Standard Edition 8.1.7.1.0
    A 3rd party application which submits hundreds of sql statements per minute, none of which use bind variables and all of which are unique statements, such as:

    insert into mytable values(to_date('2002-02-02 14:31.15',YYYY-MM-DD HH24:MI.SS'));

    Obviously, other columns are inserted also, but the date literal is making every statement unique. Bind variables are not an option.

    We run a check script every 10 minutes through cron to check the database. As part of the check, it attempts a logon. If it encounters an error, then the script emails us that the database is down. Periodically, the script cannot connect, due to the error which is the subject of this post.

    I believe the error is due to bug Bug:1397603, which is a memory leak in the shared pool. However, I need a workaround until my upgrade, and I'm not totally convinced that is the problem anyway.

    What I need is some way to measure the actual fragmentation of the shared pool, so I can flush the shared pool or at least be aware that intermittent connection failures could happen soon. I've looked at v$sgastat, but that seems essentially worthless for this purpose. I think the answer might be in x$ksmsp and/or x$ksmlru, but quite honestly I'm not sure exactly how to use them. I think this might help me:

    select ksmchcls Class,
    sum(decode(sign(ksmchsiz-4096),-1,1,0)) "count < 4k", sum(decode(sign(ksmchsiz-4096),-1,round(ksmchsiz/1024),0)) "total < 4k",
    sum(decode(sign(ksmchsiz-10240)*sign(ksmchsiz-4096),-1,1,0)) "count 4k-10k",sum(decode(sign(ksmchsiz-10240)*sign(ksmchsiz-4096),-1,round(ksmchsiz/1024),0)) "total 4k-10k",
    sum(decode(sign(ksmchsiz-10240),1,1,0)) "count > 10k", sum(decode(sign(ksmchsiz-10240),1,round(ksmchsiz/1024),0)) "total > 10k"
    from x$ksmsp
    group by ksmchcls
    /

    but I'm not sure since my problem is intermittent and whenever it has happened, the database has been bounced before I could get on it and investigate. We've kind of prioritized keeping the ability to connect higher than investigating this problem. I'm not sure I could connect to investigate anyway once the error occurs. I'm thinking about adding the above query or something similar to our check script.

    Anyone have experience with this? What did/do you do?

    Thanks!

    Kurt
    Oracle DBA and Developer

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    go to http://www.ixora.com.au and llook for Steve Adams shared pool scripts, it does what you want

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by kmesser

    Anyone have experience with this? What did/do you do?
    There is a serious shared pool fragmentation bug in 8.1.7.1 that is fixed in 8.1.7.2. I banged my head against the wall for 2 months with this problem until Oracle confirmed they had a bug and came out with the 8.1.7.2 patchset.
    Jeff Hunter

  4. #4
    Join Date
    Aug 2000
    Posts
    462
    Thanks! I know about the bug, but I can't do the upgrade right now, so I have to find an alternative to keep my instances up.

    I saw on Metalink one guy who changed the undocumented parameter _db_handles_cached or something like that, to 0 and it helped keep the instance up but slowed things way down.

    More info will be welcomed.

    Thanks Again!

    Kurt
    Oracle DBA and Developer

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