Overriding default Optimizer Mode

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.