Re: How "deterministic" are EXPLAIN PLAN results?

> 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"?

Well, the CBO is intelligent enough to adapt to the changes in the environment, to a degree, and it may produce a different plan for the same query if the environment changes alter some input values for the costing engine. However, once the query is parsed and while it's still in the shared pool, its plan won't change. Hints also reduce CBO's freedom because they specifically exclude some access paths from consideration, and chances for CBO to come up with a different plan decrease considerably. Fully hinted query has almost no chance to receive a different plan because you explicitly instructed the optimizer how you want this query to be executed (the only case when it may receive a different plan is when you alter the environment in a way, which invalidates some of your hints - drop an index, for example.)

Corrections and additions welcome.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.