Re: Execution Plan Tuning - A cautionary tale

Richard Foote wrote:
>
> 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
> > ******************************************
> >
> >

"Nah - not me...I've never made an Oracle mistake in my life..."

Signed
(insert any irritating newsgroup poster here)

:-)
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."