Author: Burleson

The Oracle cost-based SQL optimizer determines the best execution plan for SQL statements. The cost-based SQL optimizer must be equipped with all the external information that it requires.

External information of cost-based SQL optimizer:

The external information of cost-based SQL optimizer uses the dbms_stats package to collect table, index and partition information. It gets the histograms on skewed columns and foreign key table join columns. Other requirements that influence the overall behavior of the cost-based SQL optimizer include optimizer_index_cost_adj, optimizer_mode, pga_aggregate_target and optimizer_index_caching.

Determining the amount of indexes in Data Cache:

The cost-based SQL optimizer can be given a hint about the amount of indexes that exist in the Oracle data cache with the optimizer_index_caching parameter. The index blocks that in the data cache 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.

Adjusting the behavior of cost-based SQL optimizer:

The optimizer_index_caching parameter is a percentage parameter with valid values between zero and 100. This parameter adjusts the behavior of the cost-based optimizer to select the best way to access the desire SQL query results. It selects the best behavior amongst the SQL queries including hash join access, nested loop joins, full-index scans or full-table scan access.

Index Caching in buffer cache:

The cost of executing a nested loop join where an index is used to access the inner table is highly reliant on the caching of that index in the buffer cache. The amount of index caching depends on many factors including 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."

Making best access decision:

If we can separate 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.

Configuring multiple block sizes:

Starting with Oracle9i we have the capacity 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.

Better Index performance:

The indexes perform better when stored in large block size tablespaces 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.

Creating a separate 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.

Cache Buffer Allocation:

Create a region of RAM for a 32k data cache.

Allocating Tablespace:

Use 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:

Moving Indexes:

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;  

Predicting the relative accuracy:

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.

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.

Final Thoughts:

Oracle provides many tools 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.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of Oracle LOG_ARCHIVE_MAX_PROCESSES Parameter!!
   A Quick Guide to determine Oracle RAM Size!!
   Oracle Guide: Recovering accidentally dropped tables!!
   Inside Oracle Temporary Tables!!