Re: [PERFORM] Hints proposal

On Thu, Oct 12, 2006 at 11:25:25AM -0500, Jim C. Nasby wrote:
> Yes, but it does one key thing: allows DBAs to fix problems *NOW*. See
> also my comment below.

If I may argue in the other direction, speaking as one whose career
(if we may be generous enough to call it that) has been pretty much
exclusively on the operations end of things, I think that's an awful
idea.

There are two ways that quick-fix solve-the-problem-now hints are
going to be used. One is in the sort of one-off query that a DBA has
to run from time to time, that takes a long time, but that isn't
really a part of regular application load. The thing is, if you
already know your data well enough to provide a useful hint, you also
know your data well enough to work around the problem in the short
run (with some temp table tricks and the like).

The _other_ way it's going to be used is as a stealthy alteration to
regular behaviour, to solve a particular nasty performance problem
that happens to result on a given day. And every single time I've
seen anything like that done, the long term effect is always
monstrous. Two releases later, all your testing and careful
inspection and planning goes to naught one Saturday night at 3 am
(because we all know computers know what time it is _where you are_)
when the one-off trick that you pulled last quarter to solve the
manager's promise (which was made while out golfing, so nobody wrote
anything down) turns out to have a nasty effect now that the data
distribution is different. Or you think so. But now you're not
sure, because the code was tweaked a little to take some advantage of
something you now have because of the query plans that you ended up
getting because of the hint that was there because of the golf game,
so now if you start fiddling with the hints, maybe you break
something else. And you're tired, but the client is on the phone
from Hong King _right now_.

The second case is, from my experience, exactly the sort of thing you
want really a lot when the golf game is just over, and the sort of
thing you end up kicking yourself for in run-on sentences in the
middle of the night six months after the golf game is long since
forgotten.

The idea for knobs on the planner that allows the DBA to give
directed feedback, from which new planner enhancements can also come,
seems to me a really good idea. But any sort of quick and dirty hint
for right now gives me the willies.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler