Re: 10053 trace for sql fired from pl/sql (stored code)
- From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
- To: Boris Dali <boris_dali@xxxxxxxx>
- Date: Fri, 23 Dec 2005 09:08:32 -0700
Boris,
Boris Dali wrote:
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.
Not true. Just because I couldn't figure it out doesn't mean there is no
way.
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.
Aah, yes. Predicates which the CBO can use for partition elimination are
subject to the same BV peeking problems as predicates on columns with
histograms. I have to keep that in mind.
In my defense I can only say that I am dealing with SE databases lately
where partitioning (and many other features) do not enter the picture
(much less my mind :-) )
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?
Yes, I see the same "problem". The cursors (viewable via v$sql) and
their plans (viewable via v$sql_plan) are maintained separately in the
shared pool and Oracle seems to flush out plans faster than cursors.
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?
The plan_hash_value is a quasi-unique (subject to hash collisions)
signature of a plan. You can get the same plan - and therefore the same
plan_hash_value - not only for only slightly different, but even for
rather different sql statements. Just think of the simplest plan, a full
table scan. Any number of sql could result in a full table scan (of the
same table) and will therefore have the same plan_hash_value.
Before 9i when Oracle came out with the plan_hash_value I used to
extract sql statements from v$sql, run them through explain and assign a
hash value to each plan. Then repeat the process after changing some
tuning parameters (re-analyze tables, add/drop an index, init.ora
parameters) and look for plan changes which I then could scrutinize for
their performance impact. Sort of Laredo on a shoestring. With the
plan_hash_value that has become much simpler.
But, to answer your question, since so many different sql statements can
result in the same plan, you can hardly draw conclusions from the cbo
trace of one sql to the cbo trace of another, even if they eventually
result in the same plan (and plan_hash_value).
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)?
I seem to only get values into v$sql_plan_statistics when I set
statistics_level to ALL. At least reliably. Very occasionally I do see
values there without changing statistics_level from the default TYPICAL.
But I have no idea what triggers those rare occasions. They have so far
happened too infrequently for me to detect a pattern or form a hypothesis.
--
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
