The optimizer_mode is the most powerful of these silver bullet parameters and a change can radically alter the characteristics of your SQL execution workload.
To see how important the optimizer_mode is to SQL execution, consider the following three-way table join which returns 100,000 rows:
Let’s run this SQL query with several different optimizer_mode values and observe the changes to the execution plans and SQL response times:
Let’s run this SQL query with several different optimizer_mode values and observe the changes to the execution plans and SQL response times:
