Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How Reliable is Explain Plan in 9.2

RE: How Reliable is Explain Plan in 9.2

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 05 Mar 2003 18:13:42 -0800
Message-ID: <F001.00561A80.20030305181342@fatcity.com>


Let's not get overboard. It was always the premise in relational databases that
you tell the database WHAT you want and it is its job and prerogative to decide HOW
to do it. So the HOW may change at any time, but the results should not. If they do then it is a bug.

We should also clarify what "explain is unreliable" means. Through "explain plan for ..." the optimizer tells you "Given the current circumstances and my current knowledge, this is how I plan to process your sql".
If the circumstances change - e.g. add or drop an index-, or the optimizer's knowledge changes - analyzing tables/indexes changes statistics, the access plan may turn out different. That is where the "unreliability" comes in. As the
CBO evolves, it is becoming increasingly difficult to ensure that the session where and when you do the explain does have the exact same parameters as the session where the sql was executed, or will be executed. If all conditions are the same, the result of the parse will reliably be the same. It is just that it becomes increasingly uncertain that you can (re)create the runtime conditions for the
explain. Especially once the optimizer takes past execution statistics into account.

At 11:30 AM 3/5/2003 -0800, you wrote:
>Wolfgang ...
>
>Now that explain plan is unreliable and will be even more in 10i, I'll
>always keep a silver dollar in my pocket. The coin toss is still right (in
>almost) 50% of the time.
>
>Jeez ... what's next ... in 11i, SELECT statement *may* return data and in
>some cases would actually perform inserts into some other tables, because
>that's what you *wanted to do* anyways ... right?
>
>I want my dBase IV back ...
>Raj

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Wed Mar 05 2003 - 20:13:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US