Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Execution Plan Tuning - A cautionary tale

Re: Execution Plan Tuning - A cautionary tale

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 19 Jul 2002 14:05:27 +1000
Message-ID: <g7MZ8.38271$Hj3.114995@newsfeeds.bigpond.com>


Hi Niall,

The first assumption should be to never assume (particularly in Oracle).

However, who can't honestly say been there, done that !!

Not me

Richard
"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3d372632$0$8514$cc9e4d1f_at_news.dial.pipex.com...
> Remember back in school when you did your science projects that started
with
> a section
>
> Assumptions:
>
> and then you listed your assumptions and did the experiment prescribed by
> the text books and (oh so) occasionally reproduced the results that you
were
> supposed to?
>
> A similar approach might be useful in dealing with database tuning issues.
>
> You have a complex query that works reasonably well in packaged product A
> version 2.59.3 on oracle 8.1.6.1.0. You buy packaged product A version
> 2.60.4 and (following best practice) install it on 8i release 3.
>
> said query looks a bit like
>
> select <col list>
> from taba a,tabb btab1, tabb btab2, tabc agrdfe54
> where
> <25 join conditions>
> ;
>
> and uses hash joins nested loops in list iterators etc.
>
> It performs terribly on the new platform. So you analyze the tables. then
> you create histograms. then because you *do* have access to the sql you
hint
> the statement to use the access path (hash joins index access etc) that it
> did before. Then you get depressed and create a stored outline in an
earlier
> version knocked up from a technet download and move it across. then you
> release plan stability isn't supported in standard edition.
>
> Then you put it on hold a bit and go on to a performance issue with a new
> piece of functionality. Its relatively trivial and just requires a
> concatenated index. So you create it.
>
> *then and only then* do you think to yourself. "hang on a mo those columns
> were in my where clause this morning". You discover that the optimiser
> steadfastly refuses to use indexes that don't actually exist.
>
> lesson to learn. before you dive into the optimiser and performance
tuning.
> do check that database objects you know are useful haven't been lost in an
> upgrade. don't assume but check (or at least list your assumptions like
you
> did in school).
>
> grrrrrrrrrrr
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
>
>
Received on Thu Jul 18 2002 - 23:05:27 CDT

Original text of this message

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