Words of Wisdom
The one that spurred me into making a comment however was "Hinted SQL". I really liked this quote:
Hints can be very useful to solve urgent problems – but my general advice is (a) don’t use them as a first resort, (b) check whether the real problem is in the statistics (c) if you really need to hint your SQL, you probably need an average of at least one hint per table to lock in the execution path you expect and (d) assume that you’re going to have to revisit and retest any hinted SQL on the next upgrade.
(emphasis added by me)
Well said, of course if you've seen my short seminar bit "to hint or not to hint" you'd know why I say "well said"...
It is exactly what I say in conclusion during that talk. Well, almost, I state (c) a little differently, but I like Jonathan's way of saying it - so maybe I'll borrow that.
posted by Thomas Kyte at 6:00 PM
6 Comments:
I tend to avoid hinting like the plague. About the only hint that I use from time to time is the cardinality hint on Global Temp tables.
Hinting is a good way to try somethign special here and now. But one shouldn't leave hints forever in his sql.
Once I had very ugly experience of searching an answer for "what's wrong with this simple report? It worked okay for more than a year!".
Hear hear. A "fifth" point to add would be to add a SQL comment that describes 1) *why* the hint was required, 2) in what version the hint was last verified as required, and 3) what it is about the table sizes/statistics/characteristics that makes the hint better than what the optimizier has devised.
This is particularly key if you're writing 'generic' packaged software...some poor DBA on the receiving end of your hint mightn't have a clue why you're hinting a fast full index scan of a 3 million record table on a query that is run 500 times per hour...until he realizes (from your comment) that you only expected the table to be 300 records!
Totally unrelated, but what's up with the next Apress book, part two of your architecture series? Still targeted for this year?
Totally unrelated, but what's up with the next Apress book, part two of your architecture series? Still targeted for this year?
Look for it under "Tom Kite"
I hate using hints. It is admitting you don't know what you are doing. Now, that said, once I wrote a package full of explicit cursors. I had carefully crafted each one using EXPLAIN PLAN (this was long ago), yet the optimizer refused to take the most efficient execution path and I was forced to use hints. Also, working with Oracle Financials 11.3 there was no cost based optimizer, only rule based, so hints were not an option.
POST A COMMENT
<< Home