Re: 10053 trace for sql fired from pl/sql (stored code)
- From: rjamya <rjamya@xxxxxxxxx>
- To: boris_dali@xxxxxxxx
- Date: Wed, 28 Dec 2005 11:45:30 -0500
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.
>
>
> --- Tanel PÃder <tanel.poder.003@xxxxxxx> wrote:
>
> > Comments below.
> >
> > >> Couple of other questions if you don't mind:
> > >> 1) Quite often I see this case where the sql is
> > still
> > >> visible in v$sql (and its aggregate cousins -
> > >> v$sqlarea, etc) - so it's not (completely)
> > flushed out
> > >> right?, but it's gone from v$sql_plan. In fact if
> > >> stats are re-gathered or re-seeded on one of the
> > row
> > >> sources, this is also what is happening. I
> > suppose
> > >> this is because the parent sql and its plan are
> > stored
> > >> in different heaps? Than if a packaged stored
> > code is
> > >> kept in the shared pool (via dbms_shared_pool) -
> > is
> > >> the plan also guaranteed to be kept with it? But
> > if
> > >> stats are re-gathered (with default invalidate
> > option)
> > >> - plan gets flushed out regardless of the
> > "keeping"
> > >> correct?
> >
> > The cursor name (SQL statement text in case of plain
> > SQL and anonymous
> > blocks) is only stored in library cache parent for
> > space savings
> > (X$KGLOB.KGLNA). So all child cursors with same name
> > reference to the
> > parent. All child cursors do have a handle, a heap 0
> > for library cache
> > metadata where general information is held and the
> > execution plan is stored
> > in heap 6. Latter is normally bigger one as it
> > stores a "compiled"
> > representation of execution plan.
> >
> > The execution plan heap can be aged out
> > independently of heap0 and library
> > cache child handle, if it is so, then we still see
> > all child cursors in
> > library cache, but no execution plans. Having a
> > situation where heap 6 is
> > unpinned and heap 0 is pinned is quite common, heap
> > 0 is usually pinned as
> > long as there are open (or closed but cached)
> > cursors referencing it while
> > heap 6 is only pinned for the duration of actual
> > execution/call (unless
> > cursor_space_for_time = true, which keeps all cursor
> > heaps pinned all the
> > time).
> >
> > I think when we pin a cursor, then I think a bit is
> > set in cursor handle,
> > which says that whenever heaps are loaded into
> > memory, they should be
> > immediately be pinned. This prevents aging out of
> > these - however, when DDL
> > on dependent objects invalidates the cursos, the
> > unused heaps can be aged
> > out again, but as soon as we reload them (reparse)
> > these are pinned again
> > thanks to this bit in cursor handle.
> >
> > A very good whitepaper about Oracle's shared
> > pool/library cache memory
> > management is at:
> > http://download-west.oracle.com/oowsf2005/003wp.pdf
>
>
>
>
>
>
>
> __________________________________________________________
> Find your next car at http://autos.yahoo.ca
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
----------------------------------------------
This space is available for rent.
Other related posts:
