RE: 10053 trace for sql fired from pl/sql (stored code)
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: <boris_dali@xxxxxxxx>, <tanel.poder.003@xxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Dec 2005 09:58:47 -0700
Boris, have you considered using "plan stability", i.e. stored outlines?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Boris Dali
Sent: Wednesday, December 28, 2005 7:59 AM
To: tanel.poder.003@xxxxxxx; 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.
--- 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
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
