Overriding the Default Optimizer Mode for a Query in Oracle SQL Tuning

In the domain of Oracle SQL tuning, the optimizer mode plays a pivotal role. It guides the Oracle optimizer's approach to SQL queries. By default, the optimizer uses a mode specified in the init.ora file. However, specific situations might necessitate the use of a different mode for individual queries. Thankfully, Oracle allows us to override the default optimizer mode at a query level.

Before delving into the overriding process, let's quickly revisit the principal optimizer modes available in Oracle:

1. ALL_ROWS: The optimizer targets the best throughput, fetching all rows in the least amount of time.
2. FIRST_ROWS_n (n can be 1, 10, 100, or 1000): The optimizer aims to retrieve the initial 'n' number of rows as quickly as possible.
3. FIRST_ROWS: The optimizer seeks the best response time for the first row.
4. RULE: It forces the optimizer to apply rule-based optimization. Though Oracle no longer advocates this mode, it can be of value in specific contexts.

Oracle SQL provides an explicit directive for setting the optimizer mode for a single SQL statement. This option comes in the form of a hint. A hint is a special comment you can include in your SQL statement that instructs the optimizer to choose a particular approach.
Here are the steps to override the default optimizer mode for a specific query:
Insert the hint into your SQL statement: Place the hint immediately after the SELECT, UPDATE, DELETE, or INSERT keyword in your SQL query. Use the following syntax:

SELECT /*+ optimizer_mode */
...

SELECT /*+ FIRST_ROWS(100) */
...

While overriding the default optimizer mode offers granular control over query optimization, it's essential to use this power judiciously. Not all optimizer modes are suitable for every situation. Therefore, careful consideration of the specific requirements of your query is paramount.
Moreover, keep in mind that hints are suggestions to the optimizer, not orders. While the optimizer generally respects these hints, there are cases where it might choose to ignore them, especially when it determines that the hint would lead to suboptimal performance.
Ultimately, overriding the default optimizer mode for a single query can be a potent instrument in Oracle SQL tuning. However, it should be used judiciously and as part of a comprehensive SQL tuning strategy. Remember, understanding the nuances of your Oracle environment is crucial to effectively use this tool.

As you saw, it is possible to reset the default optimizer mode for all queries. However, you may also override the optimizer mode on a query-by-query basis. There are two ways to override the default optimizer mode for any query, the use of the ALTER SESSION command and the use of SQL hints.

ALTER SESSION SET OPTIMIZER_GOAL=all_rows, first_rows, or rule

SQL> alter session set optimizer_goal=rule;
Session altered.

While the ALTER SESSION command is very good for testing and development, a more surefire and permanent way of changing SQL is by using SQL hints. SQL hints are used to ensure that the access path for the SQL never changes. While there are many dozens of hints that can be added to SQL, these are the hints that change the optimizer mode for the entire statement:

/*+ all_rows */ 
/*+ first_rows */
/*+ rule */ 

Because Oracle SQL hints are embedded in comments, you will NEVER get an error message if the hint contains a syntax error. Hence, you must be very careful to run an EXPLAIN PLAN on all SQL after the hint has been added to ensure that it is doing what you intended.

Again, it is always a good idea to add a hint to “cement” the access path into every SQL statement after it has been tuned, even if the hint matches the default optimizer mode. This is great insurance against careless errors. If another DBA mistakenly changes the default optimizer mode for your database, your query will still execute at optimal performance. Now let us conclude this module with a review.