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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
