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: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Wed, 05 Mar 2003 12:29:35 -0800
Message-ID: <F001.005613A5.20030305122935@fatcity.com>


As far as I can tell, the Explain Plan is 100% reliable. It shows how the query would execute if it was run from that session at that momement in time.  

If you want to see how a query in the SGA actually ran, query the Oracle9i v$sql_plan view.  

Kevin

-----Original Message-----
Sent: Wednesday, March 05, 2003 2:30 PM
To: Multiple recipients of list ORACLE-L

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



Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !!

-----Original Message-----
Sent: Wednesday, March 05, 2003 2:09 PM
To: Multiple recipients of list ORACLE-L

Yes, explain plan will become increasingly unreliable as the cbo takes more and more factors and current conditions current into account. Some of the factors that can change the outcome of a parse from session to session are:

for Oracle 8
- different session parameters (db_file_multiblock_read_count, hash_multiblock_io_count, sort_area_size, hash_area_size)

for Oracle 9i additionally
- you can let Oracle dynamically set the sort_area_size and other memory parameters so you have a moving target now - bind variable peeking - the first parse determines the plan for all following sql depending on its bind value - system statistics in effect at the time of parse. If the system statistics get changed, existing plans do not get invalidated, but if you do an explain the cbo will use the current values - dynamic sampling where the optimizer tries to improve on its estimates by sampling predicate values at the time of parsing.

from comments I heard, it will get "worse" (as far as explain differing from reality is concerned) with Oracle 10. The optimizer will try and learn from past executions of a sql and modify the plan if appropriate.

At 08:35 AM 3/5/2003 -0800, you wrote:
>Just had a fellow tell me that explain plan in completely unreliable in 9.2

>and getting accurate results requires direct SGA access on executing SQL
(he
>is working in a RAC environment). They are running Precise, a good
product,
>but this sounds like something a sales person told him. I can only recall
>that occasionally the plan executed is not the plan you see in explain
plan.
>Anyone know the truth of this issue?

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com <http://www.centrexcc.com>


This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier électronique est une communication privée à l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'êtes pas le destinataire prévu, vous êtes avisé, par les présentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'êtes pas spécifiquement autorisé à recevoir ce courriel ou si vous croyez l'avoir reçu par erreur, veuillez en aviser l'expéditeur original immédiatement. Nous respectons les demandes similaires qui touchent la confidentialité des communications par courrier électronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_trilegiant.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 - 14:29:35 CST

Original text of this message

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