Setting the default optimizer mode in the init.ora File
View and reset the default optimizer Mode
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.
Rule vs. cost
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.
View the default optimizer mode
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
Problems with the choose mode
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.
Reset the default optimizer mode
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:
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.
Show Parameters Mode
First, click the link below to check your understanding of resetting the optimizer mode. Show Parameters Mode