Part 3 of Oracle Optimizer: Moving to and working
with CBO discusses the setup changes that should be considered for migrating to
CBO.
8. Setup changes for migrating to CBO
This
article highlights a number of key points to consider when moving to CBO. In
addition, it highlights a number of good maintenance practices. Tuning in CBO
is an ongoing process and proper analysis should be done. You may encounter
scenarios specific to your environment that are not mentioned here. Make it a
point to refer the documentation and check with Oracle support for any kind of
anomalies.
8.1) Set the Initialization parameters properly
The
parameters specified in Part
2 are very critical for your setup. Parameters like OPTIMIZER_MODE,
OPTIMIZER_MAX_PERMUTATIONS, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_INDEX_CACHING,
OPTIMIZER_FEATURES_ENABLE, COMPATIBLE etc. directly affect the optimizer.
Please provide appropriate values based on the type of environment you require.
Again, these parameters have been covered in details in Part
2.
8.2) Set the optimizer mode properly
Set
the OPTIMIZER_MODE parameter to FIRST_ROWS(_nnn) for OLTP systems. For batch
processing or data warhousing systems set it to ALL_ROWS.
Using ALL_ROWS in an OLTP system will result in a slight
(sometimes negligible) stand still before the data is shown in online screens
as Oracle concentrates on completing the query and processing of the rows
before retrieving them. On the other hand, the delay may be worse as there are
more occurrences of full table scans in ALL_ROWS than in FIRST_ROWS.
You
may also consider CHOOSE mode as an intermediate option between RBO and CBO. In
fact, CHOOSE tries to run the query in either CBO or RBO, depending on the
availability or unavailability of statistics. This is the preferred mode if the
system can be migrated in phases. Problems may arise if tables with statistics
are being used along with tables without statistics in queries; the Optimizer
may sometimes choose bad execution plans in such cases.
If
features like partitioning or materialized views are being used, related
queries will always resort to CBO mode.
8.3) Provide additional memory
I
have found it beneficial to increase the memory allocation parameter sizes by
3-10% to accommodate the additional changes/features and avoid response time
issues. Later, you can evaluate the increase or decrease in memory utilization
and adjust the parameters accordingly. Parameters DB_BLOCK_BUFFERS,
SHARED_POOL_SIZE, SHARED_POOL_RESERVED_SIZE, JAVA_POOL_SIZE and LARGE_POOL_SIZE
should be considered.
8.4) SQL and PL/SQL
The
Optimizer mode parameter is meant only for statements that are directly fired
and not for statements fired from PL/SQL. Therefore, if you have tested your
query from SQL*Plus or any other tool, and then incorporated the same in a PL/SQL
block (anonymous or stored routines), the query may or may not run the same! DML
statements from PL/SQL are run in CHOOSE mode (ALL_ROWS if statistics is
present) by default and OPTIMIZER_MODE set at session level does not influence
them.
If you have enabled CBO
in your setup, then PL/SQL statements default to ALL_ROWS. This is logical, as
stored procedures return results only after everything is processed. However,
I often find queries run better in FIRST_ROWS than in ALL_ROWS, the reason
being that indexes look more appealing in FIRST_ROWS.
For
OLTP systems, this may result in response time issues.
We
can prevent PL/SQL DML statements from running in ALL_ROWS by doing the
following.
1.
Talk to Oracle support and set the parameter _OPTIMIZER_MODE_FORCE to true.
This parameter was introduced to force the optimizer mode set at session level
to be used in PL/SQL as well. Therefore, if your session is running in
FIRST_ROWS, then the recursive SQLs (or PL/SQL statements) will also be
executed in FIRST_ROWS.
You
may evaluate the importance of this parameter in a test environment by setting
it in the initialization file or at individual session level. As this parameter
begins with an underscore, use double quotes to set it.
SQL> alter session set "_optimizer_mode_force" = true; Session altered.
2.
Use Hints and direct Optimizer to use a particular mode for individual
queries. This is a very powerful option and will become a key inclusion in
coding for the CBO environment.
I
have come across, and read about cases where setting the _OPTIMIZER_MODE_FORCE
to true did not force some specific queries to use the session level mode.
Since this is a hidden parameter not much is documented about various scenarios.
If you come across such cases, please make use of hints to direct the
optimizer.
