Oracle cost-based optimizer (CBO) is great, but sometimes it’s making wrong choices even when correct inputs are fed to it. In such cases, you need a tool to override CBOs choices, and one of the most popular tools is optimizer hints. The main reason they’re so popular is that they allow “quick-and-dirty” kind of fixes for performance issues (provided that query text can be altered). Other ways may be more reliable, but generally require more work, and who wants to do work that can be avoided? Unfortunately, there’s a well known downside to the hints — it’s very easy to run into problems if you only fix a part of the plan.
When this happens, hints can lead to terrible execution plans. For a long time, I’ve been looking for a good example to illustrate this problem, and finally this week I encountered a case which appears to be suitable for this purpose.
Oracle Diagnostician 