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: Chuck Hamilton <chuckh_at_softhome.net>
Date: Thu, 06 Mar 2003 07:29:02 -0800
Message-ID: <F001.005620E9.20030306072902@fatcity.com>


I agree with JL. You've never been guaranteed that the plan you generate after the fact is the same plan that executed at some prior time. If you're trying to optimize a query using explain plan and are concerned that the plan may change between when the SQL executed and when you ran your explain, turn on SQL tracing in your code. That's the only way you know you're looking at the plan that actually executed. And if you're concerened that the CBO may change the plan based on a dynamically changing environment, use plan stabilization (i.e. outlines).

Chuck

>
> I think there's a big emotional difference between
> "unreliable" and "won't necessarily give you the plan
> under the current circumstances that it gave at the
> time of execution" - which has been true since the
> utility came out. It is true, of course, that the reasons
> for the variation have become increasingly subtle -
> but in theory the DBA should still be sufficiently in
> control of all the necessary parameters to cater
> for the likely variations.
>
> Of course, when the real-time learning module get
> included, then we're stuffed.
>
>
>
> BTW -
>
> > - 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
>
> I read this in the manuals recently - but the first time
> I tested it, I got a plan invalidation, re-parse and new
> execution path.
>
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Coming soon one-day tutorials:
> Cost Based Optimisation
> Trouble-shooting and Tuning
> Indexing Strategies
> (see http://www.jlcomp.demon.co.uk/tutorial.html )
>
> ____UK_______March 19th
> ____UK_______April 8th
> ____UK_______April 22nd
>
> ____USA_(FL)_May 2nd
>
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA_(CA, TX)_August
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: 05 March 2003 19:09
>
>
> > 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
> >
> >
> > ********************
> >
> > 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 electronique est une communication
> privee a
> > 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'etes pas le destinataire prevu, vous etes
> avise,
> > par les presentes 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'etes pas specifiquement autorise a recevoir ce
> courriel
> > ou si vous croyez l'avoir recu par erreur, veuillez en aviser
> l'expediteur
> > original immediatement. Nous respectons les demandes similaires qui
> > touchent la confidentialite des communications par courrier
> electronique.
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Chuck Hamilton
  INET: chuckh_at_softhome.net

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 Thu Mar 06 2003 - 09:29:02 CST

Original text of this message

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