Friday, May 20, 2016

Cluster wait events "gc buffer busy release", "gc buffer busy acquire"

Whenever we have a large batch insert or a web service hitting our database, we would experience a very high level of cluster waits.

Cluster waits are grey (click on picture to enlarge)

That nice grey section there is the cluster waits.
Drilling down further, I can see that they are "gc buffer busy release", "gc buffer busy acquire" wait events.

Let's have a look at the prime contender for this wait event:
This query was an insert into a table called audit_row.
Being an audit table, almost every tables had a trigger before insert and before update inserting into that table. The trigger uses a sequence to update the primary key.

The hot object however was an index on that table.
For every new rows in the table, the sequence will provide the next value which is always incremented by 1 and this will insert in the last block of the index on the right hand side.
When the block is full, the index will split into 2 new blocks, with the previous block having all rows minus one row and the last block having one row and the rest of the block empty.

In RAC, with 3 instances all trying to write in the same block, they will acquire a lock on the block, perform their operation then release it, allowing other instances to perform their insert.

But why is this happening?
The sequences have a default cache value of 20. In a single instance, it's not too big of a deal, although increasing the cache by a larger number can increase performances a bit.

In RAC, each instances would grab the 20 next values and cache them. But they will all insert in the same block on the index.

Increasing the cache is useless if the option "ORDER" is used for the sequence.

Solution:
Ensure that if a sequence is used to generate the primary key, it has a large cache value. Use cache 10000. Ensure that NOORDER which is the default value is used.


No comments :

Post a Comment