This article provides technical insight into Oracle database Wait-Event performance issues. Wait-Event Analysis for Oracle Databases is the industry best practice today for database tuning and performance optimization. Understanding the importance of each Oracle Wait-Event or “Resource” is essential for DBAs in keeping their database performing at its optimal level. Each month, Confio Software brings you this update to provide insight into a specific Oracle Resource, its significance in your Oracle system, and how to identify and address related problems. Oracle Resource is: 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 holding 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
1. 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(
) FILE_ID, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK() 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 = AND 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. 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.
