DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2000
    We're having a problem with intermittent denial of logons due to a severely fragmented shared pool.
    AIX 4.3.2
    Oracle Standard Edition
    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?


    Oracle DBA and Developer

  2. #2
    Join Date
    Jun 2000
    Madrid, Spain
    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
    Originally posted by kmesser

    Anyone have experience with this? What did/do you do?
    There is a serious shared pool fragmentation bug in that is fixed in 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 patchset.
    Jeff Hunter
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Aug 2000
    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!

    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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.