Re: 10053 trace for sql fired from pl/sql (stored code)
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: tanel.poder.003@xxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Dec 2005 09:58:51 -0500 (EST)
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
Other related posts:
