05 Sep 2004While the Oracle cost-based SQL
optimizer (CBO) does a wonderful job in determining
the best execution plan for all SQL statements, it is
the job of the Oracle professional to ensure that the
CBO has all of the external information that it
requires. This external information includes:
- Schema statistics - Using the dbms_stats
package to collect table, index and partition
information.
- Column histograms - Get histograms on
skewed columns and foreign key table join columns.
- Parameter settings - These include
optimizer_index_cost_adj, optimizer_mode,
pga_aggregate_target and optimizer_index_caching.
These are broad-brush parameters that influence the
overall behavior of the CBO.
The Oracle professional can "hint" the optimizer
about the amount of indexes that exist in the Oracle
data cache with the optimizer_index_caching parameter.
As we may know, index blocks that are in the data
cache (db_cache_size) can be accessed without doing a
physical I/O, and are far faster. If we can tell
Oracle how much of our index blocks are expected to be
in the data cache, then the CBO can make a better
decision about whether to perform an index scan of a
full-table scan for a query. Let's take a closer look
at this important parameter.
Optimizer_index_caching
The optimizer_index_caching parameter is a
percentage parameter with valid values between zero
and 100. This parameter lets you adjust the behavior
of the cost-based optimizer to select the best way to
access the desire SQL query results:
- Nested loop joins
- Hash join access
- Full-index scans
- Full-table scan access
The cost of executing a nested loop join where an
index is used to access the inner table is highly
dependent on the caching of that index in the buffer
cache. The amount of index caching depends on many
factors, such as the load on the system and the block
access patterns of different users that the optimizer
cannot predict. Setting optimizer_index_caching to a
higher percentage makes nested loop joins look less
expensive to the optimizer, which will be more likely
to pick nested loop joins over hash or sort merge
joins.
According to the Oracle documentation:
"OPTIMIZER_INDEX_CACHING favors using selective
indexes. That is, if you use a relatively low value
for this parameter, the optimizer effectively models
the caches of all non-leaf index blocks. In this
case, the optimizer bases the cost of using this
index primarily on the basis of its selectivity.
Thus, by setting OPTIMIZER_INDEX_CACHING to a low
value, you achieve the desired modeling of the index
caching without over using possibly undesirable
indexes that have poor selectivity."
If we can segregate the index blocks into a
separate RAM area then we can accurately predict the
correct percentage for optimizer_index_caching and aid
the CBO in making the best access decision.
Starting with Oracle9i we have the ability to
configure multiple block sizes. We can define
tablespaces with block sizes of 2K, 4K, 8K, 16K, and
32K, and match tablespaces with similar sized tables
and indexes.
Many Oracle professionals still fail to appreciate
the benefits of multiple block sizes and do not
understand that the marginal cost of I/O for large
blocks is negligible. A 32K block fetch costs only 1
percent more than a 2K block fetch because 99 percent
of the disk I/O is involved with the read-write head
and rotational delay in getting to the cylinder and
track.
This is an important concept for Oracle indexes
because indexes perform better when stored in large
block size tablespaces. They perform better because
the b-trees are better balanced, and there is less
overall disk overhead with sequential index node
access.
Research by the popular author Robin Schumacher
shows that Oracle indexes built in a 32k blocksize
requires less logical I/O's for multi-block index range scans
and index fast full scans, and also shows that
indexes build with less levels.
Click here to read his
findings.
Now let's review the steps for creating a separate
index buffer.
Create an index buffer
It's easy to create a separate index buffer in
Oracle9i and we can perform the operation while the
database is active. We start by moving all indexes to
a separate tablespace, defined to a separate data
cache and then set optimizer_index_caching to the
correct value.
- Allocate a 32k cache buffer - Start by creating
a region of RAM for a 32k data cache.
alter system set db_32k_cache_size = 100m;
- Allocate a 32k tablespace - Here we use the
blocksize argument to associate the tablespace with
the data buffer. Note that with this syntax we are
using Oracle Managed Files (OMF), so we do not need
to specify the data file name:
create tablespace index_ts_32k blocksize 32k;
- Move the indexes into the 32k tablespace - This
command moves the indexes into the 32k tablespace
with no interruption to existing index queries. It
rebuilds the indexes as temporary segments, and
makes sure that the new index is usable before
dropping the old index.
alter index cust_idx rebuild online tablespace index_ts_32k;
Now that the indexes are segregated into a separate
tablespace and index buffer, we can run dictionary
scripts to predict with relative accuracy, the amount
of the indexes that we can expect to see in the RAM
index buffer.
select
value - blocks optimizer_index_caching
from
v$parameter p,
dba_segments s
where
name = 'db_32k_cache_size'
and
tablespace_name = 'INDEX_TS_32K';
This estimated value will provide a good average
for the amount of an index that can be expected to
reside in the index cache. This assume equal index
access by the application, but you can query the v$bh
view to make sure that there is no skew in index
access.
Conclusion
Oracle provides many tools for the Oracle
professional to help the CBO always make the best
decision about the way to access Oracle data. By
working toward the optimal settings you can ensure
that the majority of your SQL always executes quickly
and efficiently.
