How "deterministic" are EXPLAIN PLAN results?

From: Larry Leonard <Spam_at_DefinitiveSolutions.com>
Date: 9 Apr 2003 03:54:47 -0700
Message-ID: <27270a53.0304090254.6c48b3f@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"?

TIA. Received on Wed Apr 09 2003 - 05:54:47 CDT