|Lesson 4||Overriding the default Optimizer Mode|
|Objective||Override the default optimizer mode for a Query|
Overriding the Default Optimizer Mode for a Query in Oracle SQL Tuning
Grasping the Relevance of the Optimizer Mode
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.
Key Optimizer Modes in Oracle
Before delving into the overriding process, let's quickly revisit the principal optimizer modes available in Oracle:
- ALL_ROWS: The optimizer targets the best throughput, fetching all rows in the least amount of time.
- 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.
- FIRST_ROWS: The optimizer seeks the best response time for the first row.
- 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.
Overriding the Default Optimizer Mode for a Query
Replace "optimizer_mode" with your chosen optimizer mode, for example:
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) */
The above hint instructs the optimizer to prioritize the retrieval of the first 100 rows.
Run your SQL query: After adding the hint, execute your SQL query as usual.
Points to Ponder
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.
Using the ALTER SESSION command
If you want to override the default optimizer mode for any SQL that you execute (so long as it does not contain hints), issue the following command:
ALTER SESSION SET OPTIMIZER_GOAL=all_rows, first_rows, or rule
The snippet below shows how to do this in SQL*Plus:
SQL> alter session set optimizer_goal=rule;
Using SQL hints
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.
Hints as insurance
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.