Jonathan Lewis wrote:
>
> Notes in-line.
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
> ____Iceland__November (tbc)
> ____Belgium__November (EOUG event)
> ____UK_______December (UKOUG conference)
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____USA__October
> ____UK___November
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> "Noons" <wizofoz2k_at_yahoo.com.au.nospam> wrote in message
> news:3f8558d2$0$15970$afc38c87_at_news.optusnet.com.au...
> > >
> > > I really dislike your suggestion 4,
> >
> > That's why I put it in last order of desirability.
> >
> > > and I'm not
> > > too keen on option 1 either.
> >
> > And yet it's one of the few reliable ways a 3rd party
> > app writer has of ensuring that a given plan for
> > a given table access in a given application will
> > always be selected by Oracle.
>
> The alternative, of course, is to design the
> application properly - but that's too much
> like hard work ;)
>
> > Unfortunately, the
> > DB2 "binding" is not (yet) available in Oracle.
> >
> > Apart from gambling on a particular combination of
> > hints always doing the same across Oracle versions.
> > Far from guaranteeed this last one. I'm actually
> > surprised Oracle suggests it in their performance
> > book. They are notorious for silently removing
> > the effects of a particular hint on a point release...
> >
>
> I think 99% of the problem there has usually
> been the fact that very few people understand
> how hints work in the first case, so of course
> their effects are not stable when they are used
> incorrectly.
>
> > Of course, cloning stats is no insurance that plans
> > will be stable across databases. But it's darn near
> > close. Have yet to see one case where it didn't work.
> > Apart from obvious things like widely different DBFMR.
>
> and sort_area_size, and hash_area_size, and
> new features being silently enabled and costing
> algorithms being tweaked etc.
>
> >
> > Then we have 10g where the actual executing statement
> > can be "re-written" with new hints. That is fine, although
> > far from an optimal way of fixing this problem.
>
> Not sure quite what you are describing there - but it is
> true that Oracle 10 has the facility for collecting better
> information about the data, and therefore creating
> descriptions of better execution plans - and that's not
> really something I would call a 'fix', it's more a case
> of a perfectly rational trade: you get better plans when
> you have better information to start with (GIGO).
>
> > But
> > I doubt very much the OP was talking about that version.
> >
>
> >
> > > Fixing a problem
> > > by kicking it until it slinks away bloody and
> > > bruised is not a strategic solution - it's only
> > > going to bounce back later, probably at the
> > > most awkward possible moment.
> >
> > Isn't that what changing global optimizer performance
> > parameters to fix ONE specific problem is all about?
> > And yet it's been done ad-nauseum.
> >
> > I'd rather have a specific problem get a specific solution
> > which will NOT easily create a bucket load of others.
>
> Hacking the statistics on one table to solve one query
> is is just as likely to create a bucket load of new
> problem as fiddling with a significant optimizer
> parameter. A key difference is that sometimes,
> setting the parameter is telling the truth about
> your system - unless you know what you are doing,
> deliberately lying about table stats is unlikely to
> be telling Oracle anything useful.
>
> > That is not what one gets when fiddling with optimizer_*
> > parameters. They are by their very nature global.
> >
> > By that I mean they affect *all* SQL, not just the
> > problematic one. One setting might fix one problem
> > and be a disaster for other apps running on the same DB.
> >
> > I'm reminded of the disastrous effects in 8.* of setting
> > optimizer default to FIRST_ROWS: imp and exp performance
> > goes out the window. So do some dictionary operations.
> >
> > I was of course assuming general purpose databases.
> > Rather than systems that only run one application with
> > a limited number of tables. Those behave differently.
> >
> >
>
> I rather liked Connor's comments - take advantage of
> hints to get ideas about WHY the optimizer isn't doing
> what you expect it to do, and then fix the root cause in
> an appropriate manner.
I rather liked them too :-)
Received on Fri Oct 10 2003 - 06:26:53 CDT
