I have had an interesting case with 8.1.7 recently, working with autotrace on, and changing optimizer_index_caching and optimizer_index_cost_adj in my session ... I got the plan I wanted, but obviously with the response time I didn't want! Apparently, Oracle noticed the change, but not enough to force a reparse. optimizer_mode did it, though.
Jonathan Lewis wrote: > > 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 > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
