Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> ORA-4031 shared pool fragmentation

ORA-4031 shared pool fragmentation

From: Syltrem <syltrem_at_videotron.spammenot.ca>
Date: Wed, 14 Nov 2001 10:08:11 -0500
Message-ID: <6JvI7.3637$qb4.27139@tor-nn1.netcom.ca>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US