Re: How Reliable is Explain Plan in 9.2

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).