Re: 10053 trace for sql fired from pl/sql (stored code)
- From: Boris Dali <boris_dali@xxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Fri, 23 Dec 2005 09:17:39 -0500 (EST)
Wolfgang,
Thanks for your reply.
This is what I suspected, but wanted to get a
confirmation from an authoritative source. If Wolfgang
says you can't get a cbo trace, I know I can't get a
cbo trace.
No histograms on the tables involved, but you are dead
on with the BV peeking suggestion. This case is
somewhat similar to the one you cracked for me couple
of years ago (that also started on this list). It's
just here it's on 9i, different application and OS,
but also partition stats and different plans developed
based on different data distributions in different
partitions. In fact one of the ways I simulate
application behavior in the test-cases is by forcing
the CBO to look at different partitions on a hard
parse (the others are cardinality hint and different
seeded stats). I can confirm this from a sql trace by
verifying that BV values actually point to a partition
that would justify cbo's change of plans. But in some
cases BV values point to the same partition as
before... but the plan is changed. That's the reason I
really wanted to see cbo's calculations.
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?
2) Can I get the same plan_hash_value for slightly
different (but functionaly the same) statements (with
different hash_values of course - I fish it out from
v$sql, but than reformat it slightly so it looks less
ugly, sometimes adding [additional] hints)? If I do,
does it constitute a prove that my test-case and a
plan developed for the application are effectively the
same, meaning if I **had** an option to get a cbo
trace for the app's sql it would've matched a cbo
trace I get for my test-case?
3) Another stupid question, just taking advantage of
you being here - do you know when EXACTLY
v$sql_plan_statistics gets populated? Sometimes I get
a result set back sometime ago, and the plan is
visible in v$sql_plan, but the stats are still all
zeros in v$sql_plan_statistics. Any way to "flush" it
(like flushing index monitoring info visible in
v$object_usage, which otherwise might not appear for
some 15min)?
Thanks for your help. Merry Christmas everybody!
Boris Dali.
--- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> Boris, Dimitre,
>
> I did a bit of further checking and
> a) as you both already stated, there does not seem
> to be a way to
> have pl/sql code create a cbo trace prior to Oracle
> 10.
> b) and when I ran into that "problem" I resorted to
> Perl to get around it.
>
> Boris
> In the absence of a cbo trace one suggestion is to
> continuously
> monitor the hash_value and plan_hash_value, print
> out the plans from
> v$sql_plan, and try to draw some conclusion from the
> changed plan and
> possibly change row source estimates.
> Can the problem be due to bind variable peeking? A
> pl/sql stored
> procedure is likely using bind variables (it's
> harder to avoid it
> than not) and as you said, your shared pool is
> fairly small, so it is
> possible for the sql to age out (or is the procedure
> "kept") and
> therefore be reparsed, possibly with a different
> bind value. Are
> there histograms on any of the involved tables?
>
> At 04:50 PM 12/22/2005, Boris Dali wrote:
> >Dimitre,
> >
> >That is correct. I never had a problem with sql
> trace
> >of a stored code, it's the cbo trace that doesn't
> seem
> >to work in 9i for me
> >
> >It's very desirable though to get a cbo trace,
> because
> >this stubborn piece of stored code uses a sql that
> >seems to change execution plans "at no apparent
> >reason". So I was hoping to gain some insight into
> the
> >WHY by catching the cbo calculations "as it
> happens".
> >I don't have a problem to get a hard parse either
> via
> >"natural reasons" (this shared pool is small and it
> >ages less often sed sql fast) or via something like
> >re-collecting (or seeding) the stats on one of the
> >tables involved.
> >
> >The only problem I have it to get the cbo to emit
> its
> >calculations for me :-(
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
__________________________________________________________
Find your next car at http://autos.yahoo.ca
--
//www.freelists.org/webpage/oracle-l
Other related posts:
