Part
2 of this series covers the Initialization parameters and Hidden or Internal
Oracle parameters that influence the Optimizer in choosing execution plans. It
is very important to setup these parameters properly.
6. Initialization parameters that affect the Optimizer
Apart
from generating statistics, the setup of the below mentioned parameters play a
very crucial role in dictating how your system will work. Setting these will
depend a lot on what kind of environment you want to create, Online, Batch
processing, Data warehousing or a combination of more than one. Please note
that the optimizer considers these parameters to evaluate every execution plan
it generates in CBO.
The
parameter values that I have mentioned below in examples are settings that I
have used in some OLTP setups on Oracle 8.1.7.4 with good results. Oracle gives
you the liberty of deciding what kind of a setup you want to maintain, so do
not just keep the default values and make sure that these parameters are set as
per your requirements.
6.1) OPTIMIZER_MODE
This
will decide the mode in which the optimizer engine should run in. Valid values
are RULE, CHOOSE, ALL_ROWS, FIRST_ROWS (_n). CBO options have
been explained in Part-1 (3.2.2 – Available
CBO Modes)
Optionally,
the OPTIMIZER_MODE can be set to CHOOSE. This is kind of an intermediate option
between RBO and CBO. In fact, it tries to run the query in either CBO or RBO
depending on the availability or unavailability of statistics. Therefore, if
the tables present in the query have statistics generated on them, CBO
(ALL_ROWS only) is preferred or RBO is taken up.
e.g.:
optimizer_mode = first_rows
6.2) OPTIMIZER_FEATURES_ENABLE
This
is set to a version number such as- 8.1.5, 8.1.7, 9.0.0. Since new features and
functionality are being added to CBO in every release, its behavior may change
and result in different execution plans. You can set this to a version number for
which your application is tuned. Please note setting it to a lower version will
prevent the use of new features that have come in later versions.
e.g.:
optimizer_features_enable = 8.1.7
6.3) OPTIMIZER_MAX_PERMUTATIONS
This
parameter specifies the maximum number of permutations that should be
considered for queries with joins, to choose an execution plan. This will
influence the parse time of queries. This parameter should be set to a lower
value. Make sure the other parameters mentioned in this section are set
properly so that the optimizer finds an optimal execution plan within the
specified limits. It defaults to 80000 in Oracle 8, which means no limits! In
Oracle 9i it is defaulted to 2000.
e.g.:
optimizer_max_permutations = 2000
Another
parameter, OPTIMIZER_SEARCH_LIMIT overrides the effect of this parameter.
OPTIMIZER_SEARCH_LIMIT specifies the maximum tables in a query that would be
considered for join orders with Cartesian; it is obsolete in 8.1.6.
6.4) OPTIMIZER_INDEX_COST_ADJ
Optimizer_Index_Cost_Ad
takes a value between 1 and 10000 (default 100). Setting this value makes the
index more or less friendly to the optimizer. For example setting it to 50
tells the optimizer that an execution plan that uses index(es) is half as
expensive as other execution plans that are not using them.
The
lower the value (less than 100), the less full table scan executions will take
place in the system.
Use
of indexes in OLTP is a crucial factor for deciding the response time, but
sometimes, full table scans provide better throughput. Nested loop joins are
influenced by this parameter value. Set this parameter to a value that is
optimal for your application.
e.g.:
optimizer_index_cost_adj = 10
6.5) OPTIMIZER_INDEX_CACHING
This
tells optimizer to favor nested loops and IN-list iterators over sort-merge and
hash joins. The default value is 100 and makes nested loops and IN-list
operator look less expensive and more appealing to Optimizer. The value basically
indicates the percentage of index blocks that the optimizer should assume are
present in cache.
e.g.:
optimizer_index_caching = 100
6.6) OPTIMIZER_PERCENT_PARALLEL
OPTIMIZER_PERCENT_PARALLEL
takes a value between 0 and 100. A
low value favors indexes and a higher value will favor full table scans. The
optimizer uses this parameter in working out the cost of a full table scan. A
value of 100 makes use of degree of parallelism set at object level. I prefer
setting it to zero to favor use of indexes and prevent use of parallel query in
computing the costing.
It is renamed to _OPTIMIZER_PERCENT_PARALLEL in Oracle 9i and its value
should not be altered
unless recommended by Oracle support.
e.g.:
optimizer_percent_parallel = 0
6.7) COMPATIBLE
This
parameter is used to provide backward compatibility with earlier releases. This
may also restrict the use of some new features. CBO has undergone lot of
changes in release 8. It is advisable to set this parameter to 8.1.0 or higher.
Only three digits are required to be specified, however, you can specify more
for record purposes.
e.g.:
compatible = 8.1.7
6.8) DB_FILE_MULTIBLOCK_READ_COUNT
This
parameter determines the number of database blocks read in one input/output
operation during a full table scan. The value set is used in computing the
cost of full table scans. OLTP systems will not benefit by setting a high value
for this parameter as the computed cost for full table scan execution plans
would reduced. The maximum size is Operating system dependent.
e.g.:
db_file_multiblock_read_count = 1 (may be set to a higher value)
6.9) SORT_AREA_SIZE
This
parameter defines the maximum memory space allocated per user process that
requires sorting of data and for insert and updates to bitmap indexes.
Optimizer uses the set value for determining the cost of sorting in a query.
This defaults to 64K. Normally a size of 64K to 1M is appropriate for OLTP
systems.
Sort-merge
joins are influenced by this parameter value. The bigger the size the more
appealing will be sort-merge joins over nested-loop and hash joins. The lower
the size the more sorting will take place in temporary segments. So assign a
value that has been evaluated for your setup.
Oracle
recommends the use of PGA_AGGREGATE_TARGET instead of this parameter from
Oracle 9i.
e.g:
sort_area_size = 1048576
6.10) SORT_MULTIBLOCK_READ_COUNT
This
specifies the number of database blocks to be read each time a sort performs a
read from a temporary segment. The default value is 2. General recommendation
is to keep the default value. However, if you intend to hike up the
SORT_AREA_SIZE considerably, you may evaluate the improvement by changing this
parameter.
e.g.:
sort_multiblock_read_count = 2
