Does the Optimizer need a hint?

Today I have gone through a very useful post regarding the use of the hints in Oracle.Though there are lots of articles on the internet but Oracle Corporation has not documented the vast majority of hints.Here i am providing the link of Jonathan Lewis blog who is one of the Master in the Oracle Tunning .

http://jonathanlewis.wordpress.com/2008/05/02/rules-for-hinting/

I’ve written a number of notes about hinting in fact, by using at the “Select Category” list to the right, I see that I have (so far) tagged 26 different articles (and this will be the 27th) with the hints tag. So I’ve decided it was time that I made clear my basic guidelines on safe hinting, as follows:

1.) Don’t
2.) If you must use hints, then assume you’ve used them incorrectly.

3.) On every patch or upgrade to Oracle, assume every piece of hinted SQL is going to do the wrong thing. Because of (2) above; you’ve been lucky so far, but the patch/upgrade lets you discover your mistake.

4.) Every time you apply some DDL to an object that appears in a piece of hinted SQL assume that the hinted SQL is going to do the wrong thing. Because of (2) above; you’ve been lucky so far, but the structural change lets you discover your mistake.

You will appreciate from these guidelines that I don’t really approve of using hints. The only reason that I leave them in place on a production system is when I’m sure that there is no alternative safe mechanism for making the optimizer do what I want. (And that does mean that I will use hints sometimes on a production system.)

What I use them for on test systems is to check whether a particular execution plan is actually possible, and to track down bugs in the optimizer.

Finally, for the purposes of education, I use them to demonstrate execution plans without first having to craft data sets and set database parameters to make a plan appear ‘spontaneously’.

Always be cautious about adding hints to production systems.

For More References click below hints  : 

http://jonathanlewis.wordpress.com/category/hints/

http://www.morganslibrary.com/reference/hints.html

Enjoy     :-)