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

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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: