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.
Received on Thu Oct 09 2003 - 13:44:17 CDT
