Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








optimizer_index_caching Tips

Oracle Tips by Burleson Consulting

05 Sep 2012 - Updated 25 January 2014

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.

NOTE!  You should always make sure that you have properly gatherd dbms_stats.gather_system_stats before changing optimizer_index_caching.

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:

count(case when o.object_type= 'INDEX' then 1 end) index_blocks,
count(case when o.object_type= 'INDEX PARTITION' then 1 end)
count(case when o.object_type= 'TABLE' then 1 end) table_blocks,
count(case when o.object_type= 'TABLE PARTITION' then 1 end)
count(case when o.object_type != 'TABLE' and o.object_type != 'INDEX'
o.object_type != 'TABLE PARTITION' and o.object_type
!= 'INDEX PARTITION' then 1 end) others_blocks
   dba_objects o,
   v$bh bh
   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.

   value - blocks optimizer_index_caching
   v$parameter p,
   dba_segments s
   name = 'db_32k_cache_size'
   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.


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.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.




oracle dba poster

Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.