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)

Re: 10053 trace for sql fired from pl/sql (stored code)

From: Wolfgang Breitling <>
Date: 2005-12-23 17:08:32


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.


Wolfgang Breitling
Centrex Consulting Corporation
Received on Fri Dec 23 2005 - 17:08:32 CST

Original text of this message