What: I am seeing index contention in my RAC environment and want to convert the index into a reverse key index. Is this the best option to increase throughput and scale-ability?
Answer: When there is contention due to inserting data which is monotonically increasing as when a sequence is used there are waits like block splits that reduce the performance of the insert rate. To address this sometimes DBA’s create a reverse key which is a quick fix and does not require any application change. While this does help with the index contention, the performance of the system does not improve as much as this fix is not scalebale.
The better option is to create hash partition of the index. However when this index is on a RAC cluster there will be shipping of blocks between the nodes resulting in inter node index contention. To eliminate this further increase through put a change has to be made to the increment value (usually a sequence). The sequence being used should be pre-pended with some other value based on a specific criteria like the particular node the application connection is coming from.
To allow for better throughput on the system in a RAC cluster,