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.