If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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:

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:

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.

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.