Join hints and join ordering, or, why is the optimizer ignoring my join hint?

Have you ever tried to use a join hint (e.g., USE_NL, or USE_HASH) in a query, only to realize that the query optimizer stubbornly refuses to obey?

No matter how frustrating this might be for you, there is an explanation for this behavior. One of the main reasons for such an event is the following rule:

Oracle first decides on the join order and then on the join method to use.

In this post, we will describe how join ordering can be the reason for the optimizer to ignore a hint for forcing a specific join method (e.g., nested loops, hash join, sort merge join etc.) Moreover, we will show how one can use the USE_NL or USE_HASH hints (or any hint that forces a specific join method) in combination with the LEADING (or ORDERED) hint (i.e., hints that force a specific join order).

Using SQL Plan Baselines to Revert to an Older Plan

It's 10:00 AM and your nightly ETL flows are still running! Τhe daily reports must have been sent to the management from 8:00! From you investigation you find out that a specific statement (e.g., one that corresponds to an ETL mapping) is taking much too long than usual (it used to run in minutes and now it is running for 4 hours!). Your first thought is that some change in the execution plan must have taken place. How do you fix this? Or better, how do you fix this fast? (remember the management is waiting for the report)?

The Problem

SQL performance degradation due to a change of plan is one problem, fixing the problem fast so as to allow production ETL flows to unstack is another one, ensuring stability for the execution plan of a statement is yet another one! In a previous post, we discussed a method for detecting and verifying a change of plan. So we assume that we have verified the change of plan and we have also identified some older plan as the "good plan" that we would like to revert to.

In this post, we would like to deal with the case of how we can quickly revert to an older plan, (one that hopefully lies in the cursor cache or/and in the AWR repository) and thus fix the production problem efficiently and in a limited time-window. To this end, we will use the "SQL Plan Management" features of Oracle 11g, which apart from solving the above problem, will also ensure the required stability of the execution plan.