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.


The optimizer_index_caching parameter is a percentage parameter with valid values between zero and 100. This parameter lets you adjust the behavior of

    Requires Free Membership to View

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 elatively 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 index range scans, and important performance benefit. 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 23k;
  • 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.


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.

This was first published in September 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.