Re: How "deterministic" are EXPLAIN PLAN results?

Spam_at_DefinitiveSolutions.com (Larry Leonard) wrote in message news:<27270a53.0304090254.6c48b3f_at_posting.google.com>...
> I have a question about Oracle's "explain plan". Let's set aside the
> subject of "stored outlines" for the moment, and assume that I'm
> talking about only one database (that is, I'm not talking about
> anything *across* databases).
>
> Let's say I have a SELECT statment, and I'm considering using one of
> two hints (call them HINTA and HINTB, to avoid clouding the issue).
> When I run the SELECT with HINTA, I can get the "explain plan" for it
> from my PLAN_TABLE. Likewise for running the SELECT using HINTB. So
> far so good.
>
> My question is this. If the "explain plan" for these two queries is
> *exactly* the same, does that mean that Oracle will *definitely* use
> the same "statement execution plan" for them? Or are there ever
> circumstances in which two SELECT statements with identical "explain
> plans" will be executed with different "statement execution plans"?

Besides what everyone else said, note that the _same_ plan can work differently simply due to your indexes (for one example).

To give a strange example, since I just worked it out a few minutes ago:

I have a situation with RBO, where an update was taking way too long. There didn't seem to be much I could do about it, because the sql was being dynamically generated by an OCI generator, so for each record in a 2M row table, it would have to lookup and total a small group of records in another fair sized table. Of course, the grouping was a date range together with a dozen other fields, any of which may or may not be sent along with the OCI call based on values in another table. There was an complex index starting with a couple of low-cardinality fields, and ending with other fields not being queried on, so of course it would do a table access by rowid index range scan. After some data analysis and head-scratching, I realized that a couple of the fields were always used, and one was used in at least half, and the rest bell distributed more-or-less, so I added an index with the fields always used, followed by the date to be ranged, then the one used half the time. I'm still not sure if that last field is useful after the range or if it uses the same execution plan each time, and it still does a table access by rowid index range scan, but it's 2 orders of magnitude faster. Which means I can do it in 2 days rather than 200 (or spend days rewriting it in PL or shell).

I would imagine CBO would be _really_ bad here, since I can't send hints through the OCI generator... I suppose I could make 12**2 indices... :-)

I think CBO is deterministic in each of it's parts, but all together it is effectively non-deterministic, since too much of it is magic - we don't get enough information to make it deterministic. So we poke and prod and use plan stability.

jg

--
@home.com is bogus.
"A fibre, a fibre, my domain for a fibre!" - something I said when the
hardware guy noticed the cable wasn't long enough just before bringing
down the system.