Based on AWR report, this Blog will try to list the main checking points when using reverse key index, and shows how to find the acceptable trade-off between benefits and drawbacks. The whole discussion stems from author's experience in single instance DB (non-RAC).
1.Top 5 Timed Foreground Events
When some events like:
enq: TX - index contention
buffer busy waits
read by other session
appear in Top Events, reverse key index is hastily resorted.
However "db file sequential read" should be checked after experimenting reverse key index since all the original inserts of right-most single index block are now spread over many blocks. One should try to balance the fast logical buffer activity against slow physical disk access.
2. Time Model Statistics
Since more blocks are involved with reverse key index, DBWR is more active, and therefore:
background elapsed time
background cpu time
are augmented.
Higher "db file parallel write" and "DBWR checkpoint buffers written" can also be observed,
3. SQL ordered by Gets
The insert statement generates a lower "Buffer Gets" in case of reverse key index.
4. SQL ordered by Reads
Reverse key index incurs a higher "Physical Reads" for the insert statement, almost 1 "Reads per Exec" since each insert touches a single different block.
Moreover, the queries using the index also cause higher "Physical Reads" due to the wide spreading of conceptually adjacent indexes, but physically isolated.
5. Segment Statistics
The absolute figures of buffer contentions and physical activities can be extracted by looking:
Segments by Logical Reads
Segments by Row Lock Waits
Segments by Buffer Busy Waits
Segments by Physical Reads
Segments by Physical Writes
Based on them, a quick magnitude comparison of IO and Memory associated with system statistics can give an approximate assessment of reverse key index.
Discussion
D-1. Expert Oracle Database Architecture said:
One way to improve the performance of the PL/SQL implementation with a regular index would be to introduce a small wait. That would reduce the contention on the right-hand side of the index and increase overall performance (Page 439)
reverse key index (designed for Oracle RAC) (Page 491)
Usually, sequence insert in OLTP system is inherently operated with a slight delay due to the interactive nature of OLTP, whereas Batch processing can be impeded by heavy buffer contention of index blocks.
D-2. Introduction To Reverse Key Indexes: Part III (A Space Oddity) and
Oracle B-Tree Index Internals:Rebuilding The Truth wrote:
If the "right-most" block is filled by the maximum current value in the index, Oracle performs 90-10 block splits meaning that full index blocks are left behind in the index structure.
I prefer to call them 99-1 block splits as 90-10 is misleading.
Generally reverse key index tends to use 50-50 Split, which costs more block creates and touches.
In principle, it shifts single block buffer contention to multiple accesses of separate blocks.
D-3. Oracle MOS has a cautious Doc:
Using Reverse Key Indexes For Oracle 11.2.0.2 (Doc ID 1352477.1)
However, it must be noted that : Now the entire index had better be in the buffer cache whereas before - only the hot right hand side needed to be in the cache for efficient inserts. If the index cannot fit into the cache we might well have turned a buffer busy wait into a physical IO wait which could be even worse (the remedy is worse than the symptoms).
The reminiscent last remark is voted as the title of this Blog.
D-4. Oracle MOS Doc:
Troubleshooting 'enq: TX - index contention' Waits in a RAC Environment. (Doc ID 873243.1)
explains "enq: TX - index contention":
The reason for this is the index block splits while inserting a new row into the index. The transactions will have to wait for TX lock in mode 4, until the session that is doing the block splits completes the operations.
and suggests to look Instance Activity Stats:
root node splits
branch node splits
leaf node splits
leaf node 90-10 splits
In a DB, reverse key index can be found by:
select * from dba_indexes where index_type = 'NORMAL/REV';
