How to reduce Buffer Busy Waits with Hash Partitioned Indexes in #Oracle

fight_contention

Buffer Busy Waits can be a serious problem for large OLTP systems on both tables and indexes. If e.g. many inserts from multiple sessions occur simultaneously, they may have to compete about the same index leaf blocks like the picture below shows:

Index Leaf Block Contention

Index Leaf Block Contention

For the demo below, I’m using 100 jobs running at the same time to simulate 100 end user session that do inserts into table t with an ordinary (more...)