PL*SQL Procedures becoming corrupt / becoming uncompiled 
Author Message
 PL*SQL Procedures becoming corrupt / becoming uncompiled

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.***.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT
 PL*SQL Procedures becoming corrupt / becoming uncompiled

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

Quote:

> 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.



Wed, 18 Jun 1902 08:00:00 GMT
 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


Quote:
> 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




- Show quoted text -

Quote:
> > 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.



Wed, 18 Jun 1902 08:00:00 GMT
 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
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 --
--

Quote:

> 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



> > 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



> > > 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.



Wed, 18 Jun 1902 08:00:00 GMT
 PL*SQL Procedures becoming corrupt / becoming uncompiled
After a few fits and starts, I have successfully pinned several packages
in the shared pool using dbms_shared_pool.keep. At this point, I just
have to wait for the packages to become "invalid", and then determine if
the pinned packages are immune.  Thanks for your help.  A couple of
questions in the meantime:  1) Do I need to re-pin the packages every
time I restart the database?  If so, how do I do so?  2) Do you know of
any way to verify which procedures are currently pinned?

NOTE: The "fits and starts" I refer to above were due to an error I made
when loading dbms_shared_pool into the database.  Basically, I ran the
$ORACLE_HOME/rdbms/admin/dbmspool.sql script as user 'system' rather
than as 'sys' as is required.  In shuffling around, trying to get the
package to compile, I ran the script from svrmgrl as user 'internal'.
The result was duplicate object references for users 'sys' and 'system',
which causes Oracle a bit of heartburn.  I have since discovered that
Oracle recommends that basically every script in
$ORACLE_HOME/rdbms/admin be run as user 'sys'.  At any rate, for more
information about this situation, see oracle notes 2077829.6 & 1030426.6
(or contact me).


Quote:

> 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

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 PL*SQL Procedures becoming corrupt / becoming uncompiled
One more note: in my situation, when the packages become "invalid", they
are not being automatically recompiled.


Quote:

> 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

Sent via Deja.com http://www.deja.com/
Before you buy.


Wed, 18 Jun 1902 08:00:00 GMT
 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.


Quote:

> 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 --
> --


> > 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



> > > 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



> > > > 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.


Wed, 18 Jun 1902 08:00:00 GMT
 PL*SQL Procedures becoming corrupt / becoming uncompiled

Answers to your questions are:

1.  Yes, execute a script on startup, either of the database via a trigger
in 8i or as part of machine startup via a shell script (obviously after the
database has started).

2.  Look at the KEPT column from v$db_object_cache.

HTH.

Pete

Quote:

> After a few fits and starts, I have successfully pinned several packages
> in the shared pool using dbms_shared_pool.keep. At this point, I just
> have to wait for the packages to become "invalid", and then determine if
> the pinned packages are immune.  Thanks for your help.  A couple of
> questions in the meantime:  1) Do I need to re-pin the packages every
> time I restart the database?  If so, how do I do so?  2) Do you know of
> any way to verify which procedures are currently pinned?

> NOTE: The "fits and starts" I refer to above were due to an error I made
> when loading dbms_shared_pool into the database.  Basically, I ran the
> $ORACLE_HOME/rdbms/admin/dbmspool.sql script as user 'system' rather
> than as 'sys' as is required.  In shuffling around, trying to get the
> package to compile, I ran the script from svrmgrl as user 'internal'.
> The result was duplicate object references for users 'sys' and 'system',
> which causes Oracle a bit of heartburn.  I have since discovered that
> Oracle recommends that basically every script in
> $ORACLE_HOME/rdbms/admin be run as user 'sys'.  At any rate, for more
> information about this situation, see oracle notes 2077829.6 & 1030426.6
> (or contact me).



> > 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

> Sent via Deja.com http://www.deja.com/
> Before you buy.

  psharman.vcf
< 1K Download


Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. PL*SQL procedures uncompile / become corrupt

2. Large Databases become corrupt on a SQL 7.0 cluster

3. Can a database become corrupt after a rollback PLEASE HELP

4. Indexes become corrupted

5. Identity fields becoming corrupt?

6. Indexes become corrupted

7. TechTips: But why do tables become corrupt?

8. Help-Stored Procedures became invalid after the dump file was imported

9. Procedures become invalid after rdbms/admin scripts

10. stored procedure becomes status invalid

11. Procedures becoming invalid

12. Stored Procedure Call : Can it become a bottleneck?


 
Powered by phpBB® Forum Software