Index Block Split

Definition: As applications, users, or sessions request rows from a table, Oracle may determine through the cost-based optimizer, which index access path is best for finding rows in a table. During this index lookup process, if another session is inserting or updating data, which in turn causes updates to that index and requires an index block split, the first session must wait on that index block split until finished. After which the first session must retry the index lookup request again to get the appropriate index keys for the rows required.

Indexes are made up of a root block, branch blocks, and leaf blocks. Each of which can go through a block split. As index entries are created, and because index structures are inherently ordered, if the block required to hold the new index key is full, room must be made by performing a block split. These block splits can occur in two different flavors.

The first case splitting the block 50/50 where a new block is created and half the entries are contained in each of the blocks after the split. The second case is a 99/1 split that accommodates indexes where there are ever increasing values and the new key value is the highest key. In this case the original block is left intact and the new block contains only the new entry.

Finding the splitting index

When an index block split causes a session to wait, an event will be seen in the V$SESSION_WAIT view. The wait time associated with the event that holds up the session from selecting a row is important, but often just determining the splitting index object is key. The block splitting can then be limited by modifying the structure of the index. There are essentially three steps to this process of finding the offending index:

1. Find the data block addresses (dba) of the splitting index from the V$SESSION_WAIT view. Two different dbas are given plus the level of the index block:

   	P1 : rootdba: The root of the index P2 : level: The level of the index where the block is being split P3 : childdba: The actual block of the index being split SELECT sid, event, p1, p2, p3 FROM v$session_wait

2. Find the physical location of the splitting index by using the DBMS_UTILITY package. Two functions will help zero in on the physical location of the index block using the rootdba value from step 1:

   	DATA_BLOCK_ADDRESS_FILE: Returns the file number of the dba DATA_BLOCK_ADDRESS_BLOCK: Returns the block number the dba SELECT DBMS_UTILITY.DATA_BLOCK _ADDRESS_FILE(<rootdba>) FILE_ID, DBMS_UTILITY.DATA_BLOCK_ADDRESS _BLOCK(<rootdba>) BLOCK_ID FROM dual;

   Find the offending index object from DBA_EXTENTS using the FILE_ID and BLOCK_ID values determined from step 2:

   	SELECT owner, segment_name FROM dba_extents WHERE file_id = <FILE_ID> AND <BLOCK_ID> BETWEEN block_id AND block_id + blocks -1;

How to combat index block splits

1. Re-evaluate the setting of PCTFREE for problematic indexes. Giving a higher PCTFREE will allow more index entries to be inserted into existing index blocks and thus prolong the need for an index block split.

2. Check the indexed columns to make sure they are valid. An improperly formed index key can cause excessive splitting by the nature and order of the columns it contains.

3. 3. Check application logic. Many ill-formed applications have been known to perform excessive updates to indexes when not required. Review application code to verify all data manipulations are required, especially if some tables are treated as temporary objects when in fact they are permanent tables.

Conclusion

Through the normal processing of insertions and updates to data, Oracle must maintain indexes so future queries to the underlying data can be accessed quickly through the use of indexes. Maintaining these indexes requires Oracle to keep index entries in order and thus may need to split and move keys between blocks at times. Splitting, allocating a new block, and moving keys are very expensive operations. If done too often the performance of queries against those indexes must wait and could slow down processing. Proper detection of the offending index and taking corrective action through changing the index structure or application processing must be done.