optimizer_index_caching Tips

The optimizer_index_caching parameter tell the optimizer how much of an index (on average) in in the the RAM data buffer).  Specifically, theoptimizer_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.

 

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.  Theoptimizer_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 leavingoptimizer_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.

 

---

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_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.