 |
|
Oracle
Metric
Total Index Scans Per Sec
Oracle Tips by Burleson Consulting
|
The
Total Index Scans Per Sec
Oracle
metric is the total number of index scans per
second.
Is it possible to query the Oracle environment
and intelligently determine the optimal setting for
optimizer_index_cost_adj ? The
optimizer_index_cost_adj parameters default to a value
of 100 and can range in value from 1 to 10,000. A value of 100
means that equal weight is given to index versus multi-block reads.
In other words,
optimizer_index_cost_adj
can be thought of as a “how much do I like full-table scans?”
parameter.
With a value of 100, the CBO likes
full-table scans and index scans equally, and a number lower than
100 tells the CBO that index scans are faster than full-table
scans. However, even with a super-low setting (optimizer_index_cost_adj
=1), the CBO will still choose full-table scans against no-brainers,
like tiny tables that reside on two blocks.
In sum, the
optimizer_index_cost_adj
parameter is a weight that can be applied to the relative cost of
physical disk reads for two types of block access:
§
A single-block read (i.e. index fetch by ROWID)
§
A multi-block read (i.e. a full-table scan, OPQ,
sorting)
Physical disk speed is an important factor in
weighing these costs. As disk access speed increases, the costs of
a full-table scan versus single block reads can become negligible.
For example, the new TMS RamSan-210 solid-state disk provides up to
100,000 I/Os per second, six times faster than traditional disk
devices. In a solid-state disk environment, disk I/O is much
faster, and multi-block reads are far cheaper than traditional
disks.
|
