PL*SQL Procedures becoming corrupt / becoming uncompiled

PL*SQL Procedures becoming corrupt / becoming uncompiled

Post by catat.. » Sun, 31 Dec 1899 09:00:00



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.

 
 
 

PL*SQL Procedures becoming corrupt / becoming uncompiled

Post by Sybrand Bakke » Sun, 31 Dec 1899 09:00:00


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.


 
 
 

PL*SQL Procedures becoming corrupt / becoming uncompiled

Post by Matt Housema » Sun, 31 Dec 1899 09:00:00


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




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

 
 
 

PL*SQL Procedures becoming corrupt / becoming uncompiled

Post by markp7.. » Sun, 31 Dec 1899 09:00:00


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.

 
 
 

PL*SQL Procedures becoming corrupt / becoming uncompiled

Post by catat.. » Sun, 31 Dec 1899 09:00:00


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.
 
 
 

PL*SQL Procedures becoming corrupt / becoming uncompiled

Post by catat.. » Sun, 31 Dec 1899 09:00:00


One more note: in my situation, when the packages become "invalid", they
are not being automatically recompiled.



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

PL*SQL Procedures becoming corrupt / becoming uncompiled

Post by catat.. » Sun, 31 Dec 1899 09:00:00


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

PL*SQL Procedures becoming corrupt / becoming uncompiled

Post by Pete Sharma » Sun, 31 Dec 1899 09:00:00


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


> 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
 
 
 

1. PL*SQL procedures uncompile / become corrupt

Stored PL*SQL procedures that are compiled in the database are
uncompiling / becoming corrupt a few times a week. Most of the
procedures suffering this fate are select only, and the problem seems to
affect procedures that are heavily used. At this point I have to go
and manually recompile the procedures to work around the problem.
Obviously, this is a poor workaround, as someone has to report the
problem before I can address it. Does anyone know what sort of
situations might be causing this? Is there anyway to get Oracle to
recompile corrupt packages automatically? Are there some settings
regarding the shared pool that I should check?

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

2. Database Maintenance plans in sql server 7.0

3. Large Databases become corrupt on a SQL 7.0 cluster

4. Automatic moving of Project files

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

6. Claris HomePage

7. Indexes become corrupted

8. Sybase ODBC driver

9. Identity fields becoming corrupt?

10. Indexes become corrupted

11. TechTips: But why do tables become corrupt?

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

13. Procedures become invalid after rdbms/admin scripts