IMPORTANT NOTE! You should always make sure that you have properly
gathered dbms_stats.gather_system_stats before changing
optimizer_index_caching.
The
optimizer_index_caching parameter tell the optimizer how much of an
index (on average) in in the the RAM data buffer). Specifically, the
optimizer_index_caching parameter is used when determining the
internal cost of an index probe in a nested loops join.
A value optimizer_index_caching of 100 infers that 100% of the
index blocks are likely to be found in the buffer cache and the optimizer
adjusts the cost of an index probe or nested loop accordingly, making index
access attractive. Conversely, a value of zero for
optimizer_index_caching (the default) indicates that a nested loops
index probe is expensive because it is not in the RAM buffer, and therefore
favors, full-scan access.
As an overall average, a starting point value for
optimizer_index_caching can be computed by computing the percentage of
the database indexes are in cache. This can be used as a throttle for
favoring index access, the higher the value, the more likely that index
access will be chosen over a full scan join.
Setting optimizer_index_caching
The optimizer_index_caching parameter is
set by the DBA to help the optimizer know, on average,
how much of an index resides inside the data buffer.
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 desired 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, setting
optimizer_index_caching to a high value favors
using selective indexes over full scans.
However, Oracle recommends leaving
optimizer_index_caching to its default value of
zero because 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.
Here is a script that will display the total number
of data bocks in the data buffer and a starting value
for optimizer_index_caching:
select
count(case when o.object_type= 'INDEX' then 1
end) index_blocks,
count(case when o.object_type= 'INDEX PARTITION' then
1 end)
idx_part_blk,
count(case when o.object_type= 'TABLE' then 1
end) table_blocks,
count(case when o.object_type= 'TABLE PARTITION' then
1 end)
tbl_part_blcks,
count(case when o.object_type != 'TABLE' and
o.object_type != 'INDEX'
and
o.object_type != 'TABLE PARTITION' and
o.object_type
!= 'INDEX PARTITION' then 1 end) others_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd;
While 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.
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_cachingfrom v$parameter p, dba_segments swhere 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.
and include the URL for the page.
