Tips by Burleson
CHOOSE and RULE are no
longer supported as optimizer_mode initialization parameter
values. If you set up any of these parameters, a warning is
displayed in the alert log. The functionalities of those
parameter values still exist but will be removed in a future
release. Now, for 10g, all_rows is the default value for the
optimizer_mode initialization parameter.
Moving from RBO
to the Cost-Based Optimizer
The RBO was in use for many
years, and lots of applications were developed using the
rule-based optimizer. SQL statements were tuned manually and
optimized. Now, when you want to move the database
environment over to CBO, it is natural to want to preserve
the optimization work you have done.
By creating outlines for an
application before switching to CBO, the plans generated by
the RBO can be used. This section shows the methodology of
creating the plan outlines and using them in the new
Grant CREATE ANY OUTLINE
privilege to the schema in which the outlines will be
For example, from SYS:
- GRANT CREATE ANY
OUTLINE TO user-name
- Execute syntax similar
to the following to designate, for example, the RBGROUP
SET CREATE_STORED_OUTLINES = RBGROUP;
- Run the application
long enough to capture stored outlines for all the
important SQL statements.
- Suspend outline
generation by statement:
SET CREATE_STORED_OUTLINES = FALSE;
- Gather statistics with
the dbms_stats package.
- Change the
initialization parameter optimizer_mode to CHOOSE.
- Execute the following
statement to make Oracle use the outlines in category
SET USE_STORED_OUTLINES = RBGROUP;
Then run the application.
With this procedure for plan stability, access paths of the
SQL statements can be used.