Re: ORA-04031 Errors - Unsharable SQL

"Patrick" <nospam_at_newsranger.com> wrote in message news:V8lw7.20473$ev2.28844_at_www.newsranger.com...
> 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
>
>

flush the shared pool using a stored procedure submitted by dbms_job At least you don't have to work overtime anymore, :)

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Mon Oct 08 2001 - 12:19:07 CDT