Re: 10053 trace for sql fired from pl/sql (stored code)
- From: rjamya <rjamya@xxxxxxxxx>
- To: Boris Dali <boris_dali@xxxxxxxx>
- Date: Wed, 28 Dec 2005 12:03:25 -0500
No sure, that's why I specifically mentioned keeping the cursor instead of
the whole package. Keeping the package makes the code be 'kept'. However it
is my understanding that cursors invoked from within the package don't
inherit the 'keep' part.
Raj
On 12/28/05, Boris Dali <boris_dali@xxxxxxxx> wrote:
>
> 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 <rjamya@xxxxxxxxx> 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 <boris_dali@xxxxxxxx> 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
>
--
----------------------------------------------
This space is available for rent.
Other related posts:
