PL*SQL Procedures becoming corrupt / becoming uncompiled
| Author |
Message |
|
catat..
#1 / 8
|
 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 |
|
 |
|
Sybrand Bakke
#2 / 8
|
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 |
|
|
|
Matt Housema
#3 / 8
|
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
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 |
|
|
|
markp7..
#4 / 8
|
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 |
|
|
|
catat..
#5 / 8
|
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 |
|
|
|
catat..
#6 / 8
|
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 |
|
|
|
catat..
#7 / 8
|
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 |
|
|
|
Pete Sharma
#8 / 8
|
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 |
|
| |
|
