Monday, August 14, 2006

Words of Wisdom

Jonathan Lewis has added a couple of new writings as of August 13th 2006.

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.



Anonymous Anonymous said....

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.

Mon Aug 14, 10:47:00 PM EDT  

Blogger Anton Kovalenko said....

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!".

Tue Aug 15, 03:03:00 AM EDT  

Blogger Phil said....

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!

Tue Aug 15, 04:49:00 AM EDT  

Anonymous Anonymous said....

Totally unrelated, but what's up with the next Apress book, part two of your architecture series? Still targeted for this year?

Tue Aug 15, 10:01:00 PM EDT  

Blogger Phil said....

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"

Wed Aug 16, 04:43:00 AM EDT  

Anonymous TSMileham said....

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.

Mon Aug 21, 09:42:00 PM EDT  


<< Home