Setting the Default Optimizer Mode in the init.ora File

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

SVRMGR> 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