RAC and Index Contentions

If your application happens to be one where surrogate keys are used in abundance and if some of those tables are inserted/updated very frequently hundreds of thousands of times in an hour, then the chances are very high you would be staring at "enq: TX - index contention" waits. First, a little about surrogate keys.  Many times  data designers implement surrogate keys when there is not a good candidate for a primary key for a table.  Most often this key is implemented as a number or ID of some sort and the key is incremented using a sequence either in Oracle or the application whenever a new row is added guaranteeing uniqueness. The problem unfortunately is that the index on this surrogate key column ends up being the victim. What I mean by this is that, as new rows are inserted into the table, the surrogate key is incremented and these new values also will need to make its way into the index. So the index usually ends up seeing monotonously increasing values of the key where it has to constantly make way for new incoming values and it continues to grow. This results in the index having to constantly split its leaf blocks and continue. This unfortunately also impacts performance in applications that do a ton of inserts into such tables.  These performance issues will get exacerbated in an Oracle RAC configuration due to "buffer busy waits" or "gc buffer busy waits" and others. This is because all incoming transactions wanting to do an insert would need to wait for the index to split its blocks and they all would want to access that block simultaneously which they cannot and hence they wait. In RAC, the additional inter-instance co-ordination results in a even poorer scalability and all sorts of chaos. So what should one do, I found that one of the easiest way to deal with this is to drop the index and recreate it as a Global Hash Index.  The Hash Index helps spread the blocks around based on the values of the surrogate key such that transactions coming to insert new rows will not have to wait for the same index block since the hashing algorithm would ensure different values of the key are hashed to different blocks.