Re: Execution Plan Tuning - A cautionary tale

Just remember to use the '/*+ IMAGINARY_INDEX */' hint next time! ;-)

Yeah, I've done that before too, Niall. ;-) I'm sure we all have, at some point in the past. Made me feel about as silly as the time I did: select to_char(sysdate,'HH:MM:SS') from dual; "Hey look, Oracle is broken! The minutes aren't changing!!" ;-)

-Mark

"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 Fri Jul 19 2002 - 11:54:19 CDT