| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: 10053 trace for sql fired from pl/sql (stored code)
Boris, have you considered using "plan stability", i.e. stored outlines?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Boris Dali
Sent: Wednesday, December 28, 2005 7:59 AM
To: tanel.poder.003_at_mail.ee; ORACLE-L
Subject: 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.
> 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
-- http://www.freelists.org/webpage/oracle-l Privileged/Confidential Information may be contained in this message or attachments \ hereto. Please advise immediately if you or your employer do not consent to Internet email \ for messages of this kind. Opinions, conclusions and other information in this message \ that do not relate to the official business of this company shall be understood as neither \ given nor endorsed by it. -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 28 2005 - 17:58:47 CST
 |
 |