X-list: oracle-l Return-Path: Subject: Re: 10053 trace for sql fired from pl/sql (stored code) From: rjamya Message-id: 9177895d0512280903qeba8b26t138c0f0ea24b0f7d@mail.gmail.com Date: 2005-12-28 18:03:25 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 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 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 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.