The bad news is while resolving the buffer busy wait issue, we could have significantly impacted query performance. Suppose we optimized a query based on a nonreversed sequence number column index. But suppose because of buffer busy waits, we dropped the nonreversed key index and created a reversed key index. Now those nicely ordered sequence numbers are scattered all over the index leaf blocks. That high-performing index range scan may not perform so well now.9 In fact, the cost-based optimizer (CBO) should recognize this situation and devise another execution plan strategy. Otherwise, the query could potentially touch every index leaf block and also a large number of data blocks!

So be aware that while reverse key indexes can increase insert concurrency and throughput, they can also have a severe negative impact on previously tuned queries. As a performance analyst, your job will be to find a performance compromise or implement a creative solution (like partitioning the index or the data) to maximize performance for both insert and select operations.

Enqueues are used to lock relational and nonrelational Oracle structures in an orderly fashion. The relational structures could be Oracle data dictionary tables or application tables. For example, when Oracle updates the sys.col$ table or an application updates its employee table, enqueues are involved. If a server process is prevented from locking a table, not only will it be posting an enqueue wait event, but the lock will show in v$lock, dba_lock, v$enqueue_statistics and other views. Nonrelational structures that are locked to prevent an inappropriate change could be a library cache cursor.