Oracle Index Block Split Wait Event

As applications, users or sessions request rows from a table, Oracle may, through the cost-based optimizer, determine which index access path is best for finding rows in a table. If another session is inserting or updating data during this index lookup process, 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. This is the index block split wait event. After the index block split finishes, the first session must retry the index lookup request again to get the appropriate index keys for the rows required.

About index block splitting

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.

• 50/50 block split, where a new block is created and half the entries are contained in each of the blocks after the split.
• 99/1 block split, accommodating 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.

To find 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
   

   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.

Final thoughts

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. To maintain these indexes, Oracle must keep index entries in order and may sometimes need to split and move keys between blocks.

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.