The problem can occur when many sessions concurrently insert rows. As each insert statement gets the next sequence number, the index entry will likely be physically placed into the same index leaf block or less likely into the adjacent index leaf block. If the concurrency increases enough, sessions will need to wait a relatively long time for another session to complete its leaf block change. When the session waits, it will post a buffer busy waits event, and the buffer it is waiting on will be an index leaf block. This situation can become extremely serious and a massive performance killer.

This is actually quite easy to simulate. Simply create a table with a column that will contain a sequence number. Create an index on the sequence number column, and then create the sequence with an increasing value of one. Now have multiple sessions (perhaps only ten) begin concurrently inserting rows into the table. Make sure each of these sessions uses the next sequence number in its insert statement. Since the sequence numbers will be sequential, the indexed column contains the sequence number, and the index entries must be stored in sorted order, it is very likely that nearly all of the concurrent sessions will be making entries into the same index leaf block! With this load occurring, run any wait event report, or simply look at the v$session or v$session_wait views, and observe the sessions posting buffer busy waits events. If you determine the object based on the file number and block number (p1 and p2), it will be the index! Exciting yes, but very bad for performance.

One solution is to have Oracle store the indexed sequence numbers (1, 2, 3, and so on) with their bytes reversed. From a DBA perspective, the sequence numbers are just as they were before, but internally their bytes will be reversed. Because index entries must be stored in sorted order internally, the index entries will probably be placed into different index leaf blocks, eliminating the buffer busy waits events.