Databases Reference
In-Depth Information
reflect the true cost of an index read relative to the cost of a full table scan. The default value of 100
means that a single block read is 100% of a multiblock read—so the default value is telling the optimizer
to treat the cost of an indexed read as identical to the cost of a multiblock I/O full table scan. When you
set the parameter to a value of 50, as in this example, you're telling the optimizer that the cost of a single
block I/O (index read) is only half the cost of a multiblock I/O. This is likely to make the optimizer
choose the index read over a full table scan.
Accurate system statistics (
mbrc
,
mreadtim
,
sreadtim
, etc.) have a bearing on the use of indexes vs. full
table scans. Ideally, you should collect workload system statistics and leave the
optimizer_index_cost_adj
parameter alone. You can also calculate the relative costs of a single block
read and a multiblock read, and set the
optimizer_index_cost_adj
parameter value based on those
calculations. However, the best strategy is to simply use the parameter at the session level for a specific
statement and not at the database level. Simply experiment with various levels of the parameter until the
optimizer starts using the index.
You can also use a more “scientific” way to figure out the correct setting for the
optimizer_index_cost_adj
parameter by setting it to a value that reflects the “true” difference between
single and multiblock reads. You can compare the average wait times for the
db file sequential read
wait event (represents a single block I/O) and the
db file scattered read
wait event (represents
multiblock I/O) to arrive at an approximate value for the
optimizer_index_cost_adj
parameter. Issue the
following query to view the average wait times for both of the wait events:
SQL> select event, average_wait from v$system_event
where event like 'db file s%read';
EVENT AVERAGE_WAIT
--------------------------------- -------------------------------
db file sequential read .91
db file scattered read 1.41
SQL>
Based on the output of this query, single block sequential reads take roughly 75% of the time it takes
to perform a multiblock (scattered) read. This indicates that the
optimizer_index_cost_adj
parameter
should be set to somewhere around 75. However, as mentioned earlier, setting the parameter at the
database level isn't recommended—instead, use this parameter sparingly for specific statements where
you want to force the use of an index. Note that setting the parameter at the session level requires the
DBA to grant the user privileges to issue the
alter session
command (or you must do it through some
other procedural mechanism). A good alternative to having to issue the
alter session
statement is to
specify the
OPT_PARAM
hint to change the parameter for a specific query, such as
/*+
opt_param('optimizer_index_cost_adj',50) */
. That way, it is only for the specific query and doesn't
require any special privileges. Note that an
OPT_PARAM
hint allows you to set the value of an initialization
parameter for just the duration of the current query, and you can use it to set values for a handful of
initialization parameters, including the
optimizer_index_cost_adj
parameter.
