Re: PL*SQL Procedures becoming corrupt / becoming uncompiled

Is there any method to predict how big of a hit on the SGA it is when pinning procedures? What's the heuristic? TIA,
Matt Houseman

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:953228903.12904.0.pluto.d4ee154e_at_news.demon.nl...
> This occurs because they are validated each time they are called.
> The problem usually occurs most frequently when procedures call each
 other,
> this can result in deadlock on the datadictionary.
> If they are really heavily used you should pin them in the shared pool.
> At least this prevents revalidation.
> Use dbms_shared_pool.keep('<USERNAME>.<PROCEDURENAME>')
>
> Invalid procedures are recompiled automatically.
> Alternatively you could try
> dbms_utility.compile_schema('<SCHEMANAME>');
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> <catatony_at_my-deja.com> wrote in message
 news:8ar5pf$sgc$1_at_nnrp1.deja.com...
> > Several times per week, several of the procedures stored in my
> > production database are becoming corrupt / becoming uncompiled. The
> > procedures affected are mostly select only, and most of them are heavily
> > used. My only workaround at this point is to manually recompile the
> > procedures. This is a poor workaround as some user must experience a
> > problem before I become aware of the problem.
> > What could be causing the procedures to become corrupt? Is there
> > potentially a shared pool problem? Is there any way to get Oracle to
> > automatically recompile corrupted procedures?
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>
Received on Thu Mar 16 2000 - 00:00:00 CST