Re: 10053 trace for sql fired from pl/sql (stored code)

From: Boris Dali <boris_dali_at_yahoo.ca>
Date: 2005-12-28 17:55:01
Message-id: 20051228165501.77880.qmail@web32804.mail.mud.yahoo.com

Raj,

Right. But keeping a whole package would probably achieve the same thing, would it not? It is the second part I am not sure about - the plan - would it be kept (using either option)? After all, this keeping thing is for shared pool space management, not for plan keeping, but would be nice it had this side effect (even with a price of setting of
cursor_space_for_time)

Thanks,
Boris Dali.

• rjamya wrote:

> Not tried this, but Boris can you use
> dbms_shared_pool.keep to "keep" the
> cursor in the shared_pool and probably that would
> cause the execution plan
> to also remain?? Not keeping the package, but just
> the cursor in question
> ... i.e. flag => 'C'
>
> Just a theory though
> Raj
>
> On 12/28/05, Boris Dali wrote:
> >
> > Tanel,
> >
> > Thanks a lot for the info. And thank you for the
> white
> > paper - haven't seen it before. It will take me
> > sometime to digest all this though, so just to get
> one
> > thing straight - if I want an execution plan to
> stay
> > the same until next time stats are re-collected
> (and
> > assuming that plan **CAN** be shared, e.g. BV
> values
> > in the same range, etc.), would keeping a package
> > (that contains my embedded sql statement) help? I
> mean
> > does keeping affects BOTH heap 0 **AND** heap 6 or
> an
> > execution plan for the kept sql can still be aged
> out,
> > as it happens quite often with a normal non-kept
> sql?
> > Or cursor_space_for_time has to be set as well? (I
> > know it needs larger pool)
> >
> > Is there a better way to ensure an execution plan
> for
> > a specific sql stays the same as long as stats are
> not
> > re-gathered (I don't want to disable BV peeking to
> > favor composite stats nor make shared pool too
> big)?
> >
> > Thanks,
> > Boris Dali.
                                  

---

Find your next car at http://autos.yahoo.ca

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 28 2005 - 17:55:01 CST