You are here

Using reverse key indexes to solve buffer busy wait problems

Buffer busy wait and related events can cripple performance of concurrent inserts. Bad in a single instance database, far worse in a RAC (think "gc buffer busy"). Often the problem is because of a primary key populated from a sequence. Reversing the index can fix this problem.

Contention for index blocks when inserting grows can cause an application to hang up completely. This is because with a b-tree index on a monotonically increasing key, even though there will never be row lock all the inserted keys are going onto the same block at the edge of the index. A reverse key index will fix this. If you index, for example, 19, 20 and 21 as themselves, all three keys will probably be in the same block of the index. Instead, you would index them as 91, 02, and 12. So consecutive values will not be adjacent in the index: they will be distributed across the whole width of the index. You could do this programmatically, but Oracle provides the reverse key index for exactly this purpose. Here's an example:

The schema JW now has two tables, one indexed with a normal b-tree and the other with a reverse key b-tree, and a sequence to generate the keys. And two procedures to insert some rows.
Now set up a concurrency test, using Windows shell scripts:

The SQL*Plus script I.SQL does nothing more than execute a procedure. The batch file CONCURRENT_INSERTS.BAT will launch SQL*Plus, calling the script I.SQL with a command line argument that will pass the name of the procedure.
To run the test, the FOR loops call the batch file concurrently in a hundred background sessions, performing ten thousand inserts each.
What is the result for buffer busy wait? After running that test? Here it is:

The reverse key index has reduced the buffer busy waits by around 95%. Impressed? I hope you are. This is even more significant in a RAC environment, where buffer busy wait is globalized.
I am not saying that all indexes should be reversed. You do need to understand your data and how it is being accessed. For example, a non-equality predicate on the key cannot use the index. But when would you use a non-equality predicate on a primary key? Probably, never. It is hard to find a reason for not reversing all your monotonically increasing keys.

----
Addition - by popular demand: some timings

I enabled trace for the test, these are the results (insert into the normal index first, then the reversed index):

Sorry, I do not understand what you mean by these terms. But reverse key indexes is a pretty straightforward topic, I'm sure if you run a few experiments you can work out anything that isn't clear. That is what I always do.