| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PL*SQL Procedures becoming corrupt / becoming uncompiled
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
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
ksmchcls Status
from x$ksmsp
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'
-- 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:Received on Fri Mar 17 2000 - 00:00:00 CST
> 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.
 |
 |