Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> ORA-4031 shared pool fragmentation
What is you best SQL script to identify & troubleshoot shared pool
fragmentation?
Our main application uses bind variables so all SQL must be sharable but we
have no control over them.
We also have some applications (some in-house, some not) using ODBC drivers.
As I believe we have more and more of those, I suspect they may be the
problem. We did not have ORA-4031 for over 18 months, and now it starts to
happen. The only way to clear it it to bounce the db. ALTER SYSTEM FLUSH
SHARED_POOL does not help any single bit.
So far I only increased the SHARED_POOL_SIZE from 80M to 100M after the problem occured a 2nd time after 10 days of operation, but I doubt this will help in the long run.
We do have more users connected than what we had 6 months ago, but all use the same application as was used before (apart from the new ODBC stuff used by a handful of users). We usually have about 150-175 users connected.
Oracle 81600
OpenVMS 7.201
Main application engine is Gembase
select max(KSMCHSIZ) "Max free contiguous" from x$ksmsp where KSMCHCLS=
'free';
Max free contiguous
4112
Any hint appreciated.
Syltrem
http://pages.infinit.net/syltrem (OpenVMS related web site - en français)
To reply to myself directly, remove .spammenot from my address
Received on Wed Nov 14 2001 - 09:08:11 CST