How to Set the Default Optimizer Mode in the init.ora File for Oracle SQL Tuning

The init.ora file is an indispensable element in the realm of Oracle database management. It is a parameter file that Oracle uses to control the behavior and characteristics of an instance. It sets the scene for the smooth operation of your database and thus deserves your attention.

When diving into Oracle SQL tuning, you may find yourself grappling with a variety of parameters. One of the most critical is the optimizer mode. This setting dictates how the Oracle optimizer responds to SQL queries, with its performance impacting your entire Oracle environment. By adjusting the optimizer mode, you can change the optimizer's approach towards queries, hence improving SQL performance. This change might be the difference between an optimally functioning system and a lagging one.

The Oracle optimizer has several modes that can be set as default:

1. ALL_ROWS: This mode tells the optimizer to aim for the best throughput.
2. FIRST_ROWS_n (where n can be 1, 10, 100, or 1000): In this mode, the optimizer works to retrieve the first 'n' number of rows.
3. FIRST_ROWS: Aims for the best response time for the first single row to be returned.
4. RULE: Makes the optimizer apply rule-based optimization. Although Oracle no longer recommends this mode, it can still be helpful in certain situations.

Now, let's delve into the process of setting the default optimizer mode in the init.ora file:

1. Locate the init.ora file: The location of the init.ora file depends on your Oracle setup. Generally, it is found in the $ORACLE_HOME/dbs directory.
2. Open the file: Use a text editor of your choice to open the file.
3. Add or modify the optimizer_mode parameter: Locate the line that contains the phrase "optimizer_mode". If it does not exist, you need to add it. The syntax is as follows:

optimizer_mode = mode

optimizer_mode = ALL_ROWS

SQL> shutdown immediate
SQL> startup

While setting the default optimizer mode is straightforward, it is essential to consider the implications. Understand your system requirements before making any changes. Test the impact of the change on a non-production environment first to ensure it aligns with your system's needs.
Also, remember that the default optimizer mode is a system-level setting. It applies to all sessions unless overridden at the session or query level.

In conclusion, the init.ora file is a powerful tool in the hands of a skilled Oracle SQL tuning expert. By setting the default optimizer mode, you can influence how the Oracle optimizer treats SQL queries, potentially unlocking significant performance improvements. However, remember that this is not a magic wand that will solve all performance issues, and its usage should be part of a comprehensive Oracle SQL tuning strategy.

As we mentioned in the last lesson, the optimizer_mode parameter sets the default SQL optimizer mode for all queries on the database. By default, we mean the mode that will be used if there are not any overrides, such as SQL hints or ALTER SESSION commands.
There are four values for optimizer_mode. The table below summarizes them for you.

Whenever an EXPLAIN PLAN shows that full-table scans are being performed where indexes can be used, then the SQL should be re-explained with the rule hint. The cost-based optimizer has a problem with complex table joins.
Whenever the rule-based optimizer chooses the “wrong” index to access a table, try re-explaining the query with the all_rows hint. This cost-based optimizer is better at evaluating the correct index on a table.

There are several techniques for viewing the default optimizer mode for your database. The most reliable way is to enter server manager and issue the code displayed below

SQL> show parameters mode

NAME                      TYPE    VALUE
------------------------- ------- ------------
optimizer_mode            string  CHOOSE                        
remote_dependencies_mode  string  TIMESTAMP                               

The default optimizer_mode with Oracle is choose. As we discussed, this can inadvertently cause problems if any SQL query spans some tables that contains statistics and other tables that do not contain statistics.
I strongly recommend that this default be changed to “rule” or “cost,” depending upon the needs of your SQL queries.

To reset the default optimizer mode, all you need to do is edit the init.ora file in your $ORACLE_HOME/dbs directory. Once edited, your changes will take effect after you shut down and re-start the database. Here is what you would enter in order to change the optimizer mode from choose to all_rows using vi:

cd$ORACLE_HOME/dbs
vi init$ORACLE_SID.ora
<esc> /optim
<esc> :./$s/choose/all_rows/g
<esc> :x

Remember, the default mode is only used when the SQL does not have an optimizer directive. The optimizer_mode settings only take effect when neither optimizer_goal settings nor hints are present.
Also, it can be very dangerous to change the system-wide default optimizer mode. For example, it may be tempting to change your default mode from “rule” to “first_rows.” However, we must remember that this action will have a dramatic effect on the SQL, causing some queries to run faster, while other queries will run slower.
The safest approach is to require that all SQL that has been tuned contain an optimizer hint (rule, first_rows, or all_rows). In this fashion, changing the default will not change the behavior of pre-tuned SQL. The next lesson will show you how to do this.

First, click the link below to check your understanding of resetting the optimizer mode.
Show Parameters Mode