Re: PL*SQL Procedures becoming corrupt / becoming uncompiled

By "corrupted", I definitely mean "invalid". We do make schema changes from time to time, which has co-related with package invalidation. However, package invalidation is most often occuring with out a co-related schema change. The application we are using does not use work tables that are dropped and then recreated. I suspect that we may have some shared pool sizing issues causing the fetch problems you refer to. Our user base is growing rapidly, and spikes in the amount of used shared_pool are likely. Thanks for the script reference - I will use it and report interesting results, should any result.

In article <8athc5$ik4$1_at_nnrp1.deja.com>, markp7832_at_my-deja.com wrote:
> In Catatony's original post he asked: "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?"
>
> I am reading 'corrupt' to mean invalid. As I understand it stored
> procedures and packaged code normally only goes invalid if a
 referenced
> object in the code, i.e., a table or called procedure, has a DDL
> operation applied to it. Flushing of stored procedures should not
> cause them to go invalid, but only cause them to be reloaded into the
> pool. This leads me to question if the application is dynamically
> dropping and re-creating any work tables referenced by the stored code
> or if application changes are being applied to the system while it is
> supporting production.
>
> Since Oracle will automatically recompile invalid objects when they
 are
> referenced in an executed statement it should technically not be
> necessary to manually recompile them unless some of the automatic
> recompiles are failing due to lack of free space in the shared pool.
> This should only happen if the stored code is large or you shared pool
> is way too small.
>
> Another possible problem is that free space to hold the objects is not
> available in the pool to fetch the object. If some of these routine
> are large then pinning them on instance start-up would help. If some
> of the large procedures are not used that much so you do not want to
> pin them, or you are getting errors trying to load them into the pool
> you may want to look at the init.ora parameters:
> shared_pool_reserved_size and shared_pool_reserved_min_alloc which you
> can use to reserve space for large objects.
>
> Here is an sql routine that may be of use:
> set echo off
> rem
> rem SQL*Plus script to display shared pool memory usage and free
> percentage
> rem
> rem 19980622 Mark D Powell Display free memory in shared pool
> rem 19990809 Mark D Powell Add x$ select taken from July '99 select
> Mag
> rem by R Niemiec; Mod to show mem by usage;
> rem 19991213 Mark D Powell Mod first query order 2 put descr b4
> stats
> rem
> set pagesize 90
>
> column Value format 999,999,990
> column Bytes format 999,999,990
> column Free format 990.9 heading 'Free |Percentage'
> rem
> accept sys_pass prompt 'Enter SYS password ==> ' hide
> connect sys/&sys_pass
> rem
> select
> ksmchcom
> ,sum(ksmchsiz) Bytes
> ,ksmchcls Status
> from x$ksmsp
> group by ksmchcom, ksmchcls
> /
> rem
> select sum(ksmchsiz) Bytes,
> ksmchcls Status
> from x$ksmsp
> group by ksmchcls
> /
> rem
> select to_number(p.value) "Total|Pool",
> s.bytes "Free|Bytes",
> round(( s.bytes / p.value ) * 100,1) "Free"
> from v$parameter p,
> v$sgastat s
> where p.name = 'shared_pool_size'
> and s.name = 'free memory'
> /
> rem
> undefine sys_pass
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
> --
> In article <8asdmf$a4k$1_at_nntp9.atl.mindspring.net>,
> "Matt Houseman" <mhousema_at_ix.netcom.com> wrote:
> > 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 17 2000 - 00:00:00 CST