 Oracle
Tips by BurlesonOracle10g
Optimizer Mode
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
environment.
Grant CREATE ANY OUTLINE
privilege to the schema in which the outlines will be
created
For example, from SYS:
- GRANT CREATE ANY
OUTLINE TO user-name
- Execute syntax similar
to the following to designate, for example, the RBGROUP
outline category.
ALTER SESSION
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:
ALTER SESSION
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
RBGROUP:
ALTER SESSION
SET USE_STORED_OUTLINES = RBGROUP;
Then run the application.
With this procedure for plan stability, access paths of the
SQL statements can be used.
|
