I just stumbled on Metalink article 1603359.1, which recommends that ATG customers encountering contention on the ORDER_LASTMOD_IDX index on table DCSPP_ORDER drop the index. It's noted that this is especially likely in RAC and Exadata.
The indexed column, LAST_MODIFIED_DATE (a TIMESTAMP), frequently changes for an order and every order being changed will be adding or updating nearly the same value. This means that every session will be attempting to add data in the same index leaf, meaning there will be heavy competition for that block in RAC as each node attempts to capture it.
The index will also grow significantly faster than its data. Say an order goes through a series of ten updates; each will require that the "old" leaf be cleared of its value and a new one added further "up" the index (we're talking about a timestamp after all). An order updated ten times will leave nine empty leaves behind, which will never be reclaimed since a record will never be updated to a past time.
This can certainly be dealt with by rebuilding the index periodically, but it doesn't solve the contention issue. For shops using Standard Edition, rebuilding an index comes at the cost of locking the table, effectively causing an outage during the maintenance. Dropping the index comes at the expense of poor performance of some ATG queries that search on LAST_MODIFIED_DATE.
But there's another solution—rebuilding the index with the REVERSE keyword.
A reverse key on the index will spread activity across ten "hot" blocks instead of just one. That's because, instead of entering a timestamp based on the incremental time with the least volatile components (Year, month, day) occupying the most volatile spots (the leading edge of the index) a reverse index on this column will put the most volatile portion first (the seconds/milliseconds) and the least volatile last.
The index also won't fragment, either, since the "empty" spots in the index can be reused. Under the default configuration we would see this index grow to 11G or more in a matter of months, when the table itself was under 3G. Since converting the index to be a reverse key, it's remained at a much more stable (and manageable) 1G despite the table growing to over 5G.
Waits, in particular Global Cache waits associated with RAC attempting to obtain a cached block from another node, virtually disappeared for us.
The only downside is that any BETWEEN condition against the LAST_MODIFIED_DATE will not use the index, since a reverse key index can't be used for a range scan.
