RE: source of package invalidation
- From: "Stephens, Chris" <chris_stephens@xxxxxxxxxxxx>
- To: "Barun, Vlado" <Vlado.Barun@xxxxxxx>, <kutrovsky.oracle@xxxxxxxxx>
- Date: Fri, 13 Mar 2009 09:51:12 -0500
Thank you for all the suggestions!
We have a pretty retarded process in place that makes use of a series of
materialized views built on other materialized views that were created to
alleviate some performance problems. These materialized views are kept fresh
through a procedure invoked through database change notification. At some
point we ran into problems where the top most views weren't reflecting the most
recent data because 1 or more views which they were built on were invalid and
consequently didn't refresh through dbms_mview.refresh. To solve this problem,
we first compile each materialized view in the hierarchy through 'execute
immediate'. The lack of successful refresh was determined to be a bug by
oracle support and the workaround was the execute immediate stuff.
Of course the package in question that is called by some sql executed from a
cold fusion page is dependent on one or more of those materialized views.
Hence the problem.
Ugh.
-----Original Message-----
From: Barun, Vlado [mailto:Vlado.Barun@xxxxxxx]
Sent: Friday, March 13, 2009 9:42 AM
To: kutrovsky.oracle@xxxxxxxxx; Stephens, Chris
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: source of package invalidation
If logminer is not an option, you might want to consider enabling auditing on
any changes to PL/SQL objects.
Then you can execute the query below (or something similar) to see if the
object in question (ie. x.y) or any of its dependents have been altered.
FYI, I agree that logminer is the most comprehensive option...
select timestamp, username, action_name, owner || '.' || obj_name as obj,
RETURNCODE, userhost
from dba_audit_trail
where
timestamp between to_date('2009-03-11 03', 'yyyy-mm-dd hh24') and
to_date('2009-03-11 15:33', 'yyyy-mm-dd hh24:mi')
and (owner, obj_name) in
(
Select owner
, object_name
from sys.DBA_OBJECTS do,
(
Select referenced_object_id as object_id
, level as dc_level
, rownum rn
from public_dependency
connect by prior referenced_object_id = object_id
start with object_id =
(
Select object_id
from sys.DBA_OBJECTS
where owner || '.' || object_name = 'X.Y'
)
) dc
where do.object_id = dc.object_id
union
select 'X'
, 'Y'
from dual
)
order by timestamp
Regards,
Vlado Barun, M.Sc.
Sr. Database Architect/Manager, Database Engineering and Operations
Jewelry Television
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christo Kutrovsky
Sent: Friday, March 13, 2009 10:19 AM
To: chris_stephens@xxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: source of package invalidation
Chris,
Logminer is your best friend. Get the invalidation time (if possible)
and look what happens in that period.
You will see inserts/updates to system tables when the package
invalidates and recompiles. You can see what happens just before that.
We had similar problem but in our case the procedure in package was
running for a couple of minutes. You can't recompile a package that is
been executed, so you wait. You can't run a package that's waiting to
be recompiled, so everyone waits.
The end result is a locked up system. In our case it was dynamic DDL
that was "forgotten". LogMiner does not forget.
--
Christo Kutrovsky
Senior DBA
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
On Thu, Mar 12, 2009 at 11:23 AM, Stephens, Chris
<chris_stephens@xxxxxxxxxxxx> wrote:
> We are seeing packages invalidated in our production database and I don't
> have a clue how to track down the actual cause.
>
>
>
> I've taken a look at dba_dependencies and know the potential candidates for
> causing the invalidation but I'm looking to actually identify the chain of
> events that is causing this.
>
>
>
> Is there an oracle event that can be set to generate a trace file for
> something like this?
>
>
>
> Any other ideas on how to track this down?
>
>
>
> Thanks!
>
> Chris
>
>
>
>
> CONFIDENTIALITY NOTICE:
> This message is intended for the use of the individual or entity to which it
> is addressed and may contain information that is privileged,
> confidential and exempt from disclosure under applicable law. If the reader
> of this message is not the intended recipient or the employee or agent
> responsible for delivering this message to the intended recipient, you are
> hereby notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you have received this
> communication in error, please notify us immediately by email reply.
>
>
>
--
Christo Kutrovsky
Senior DBA
The Pythian Group - www.pythian.com
I blog at http://www.pythian.com/blogs/
--
//www.freelists.org/webpage/oracle-l
CONFIDENTIALITY NOTICE:
This message is intended for the use of the individual or entity to
which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader of
this message is not the intended recipient or the employee or agent responsible
for delivering this message to the intended recipient, you are hereby notified
that any dissemination, distribution or copying of this communication is
strictly prohibited. If you have received this
communication in error, please notify us immediately by email reply.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
