Path: news.easynews.com!easynews!feed2.onemain.com!feed1.onemain.com!newsfeeds.belnet.be!news.belnet.be!transit.news.xs4all.nl!newszilla.xs4all.nl!not-for-mail Message-ID: <3BC3E93C.4C2E0C1F@office.casema.nl> Date: Wed, 10 Oct 2001 08:22:52 +0200 From: Berno Strik X-Mailer: Mozilla 4.5 [nl] (WinNT; I) X-Accept-Language: nl MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: ORA-04031 Errors - Unsharable SQL References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 31 NNTP-Posting-Host: 195.96.96.6 X-Trace: 1002695005 newszilla.xs4all.nl 19380 bstrik/195.96.96.6 Xref: easynews comp.databases.oracle.server:120760 X-Received-Date: Wed, 10 Oct 2001 03:12:09 MST (news.easynews.com) Hello Patrick, You should also look on metalink at bug 1649372. It describes the same symptoms. It can be fixed by setting the undocumented parameter: _db_handles_cached=0. HTH Berno Patrick schreef: > I have a database that supports a custom developed application and it is > currently suffering from a large number of ORA-04031 errors. I have read > everything I could find on MetaLink and am reasonably assured that I have found > the problem. I believe that one of the stored procedures is unsharable. > The sysmptoms I am seeing is that the free memory in the SGA is rapidly > decreasing at the same time that the sql area is rapidly increasing. Upon > querying the sql area, I foind that one procedure appears around 600 times in > less than hour. Each procedure call is being considered as a seperate SQL > statement, probably because of the use of literals in the call. > My question is, does anyone have any good resources/documentation for fixing > such a problem? The app was developed by an internal development team at one of > our remote locations. This is their first foray into both SQL and JAVA > programming. The application uses servlets and JSP's with WebLogic as the app > server. At this point I am most concerned with them making the procedure call > sharable, so documentatio that describes this in detail would be most desirable. > Currently, I must flush the sahreed pool every 6 hours to prevent the ORA-04031 > errors. If anyone has a better short term solution I would be most interested > in hearing it. Thanks in advance for any input you can provide. > > later ........ > Patrick