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: Stephane Faroult <sfaroult_at_oriole.com>
Date: Wed, 05 Mar 2003 13:40:58 -0800
Message-ID: <F001.005616CE.20030305134058@fatcity.com>


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" <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
> >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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 - 15:40:58 CST

Original text of this message

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