Configuring the Query Optimizer - Troubleshooting Oracle Performance - page 283

To avoid this kind of instability, I usually don't recommend setting the optimizer_index_cost_adj initialization
parameter to low values. It's also important to mention that system statistics provide an adjustment of the cost
associated to full table scans. This means that if system statistics are in place, the default value is usually good. Also
notice that system statistics don't have the same drawbacks as this parameter does, because they increase the costs
instead of decreasing them.
The optimizer_index_cost_adj initialization parameter is dynamic and can be changed at the instance and
session levels. In a 12.1 multitenant environment, it can also be set at the PDB level.

optimizer_index_caching
The optimizer_index_caching initialization parameter is used to specify the expected amount (in percent) of index
blocks cached in the buffer cache during the execution of in-list iterators and nested loop joins. It's important to note
that the value of this initialization parameter is used by the query optimizer to adjust its estimations only. In other
words, it doesn't specify how much of each of the indexes should be cached by the database engine. Valid values
range from 0 to 100. The default is 0. Values greater than 0 decrease the cost of index scans performed for in-list
iterators and in the inner loop of nested loop joins. Because of this, the optimizer_index_caching parameter is used
to increase the utilization of these operations.
Formula 9-4 shows the correction applied to the index range scan costing formula presented in the previous
section (Formula 9-3).

Formula 9-4. I/O cost of table accesses based on index range scans

  optimizer_index_caching 

( )

io_cost ≈ blevel+leaf_blocks selectivity ⋅ ⋅ − 1 +



 

100

 



 optimizer_index_cost_adj

clustering_factor selectivity ⋅ ⋅

 

100

This initialization parameter shares some of the drawbacks described in the previous section about the
optimizer_index_cost_adj initialization parameter. Nevertheless, its impact is less widespread mainly because
of two reasons. First, it's used only for nested loops and in-list iterators. Second, it has no impact on the clustering
factor part of the costing formula used for index range scans (Formula 9-4). Because the clustering factor is frequently
the biggest factor in the costing formula, it's less likely that this initialization parameter leads to wrong decisions. In
summary, this initialization parameter has less impact on the query optimizer than the optimizer_index_cost_adj
initialization parameter does. That said, the default value is usually good.
The optimizer_index_caching initialization parameter is dynamic and can be changed at the instance and
session levels. In a 12.1 multitenant environment, it can also be set at the PDB level.

optimizer_secure_view_merging
The optimizer_secure_view_merging initialization parameter is available to control query transformations like view
merging and predicate move around (refer to Chapter 6 for a description of these query transformations). It can be set
to either FALSE or TRUE . The default is TRUE .

FALSE allows the query optimizer to apply query transformations without checking whether
doing so could lead to security issues.

•

TRUE allows the query optimizer to apply query transformations only when doing so won't
lead to security issues.

•