19c生产enq: FB - contention一例

Troubleshooting 'enq: FB - contention' Format Block Enqueues. (Doc ID 1379986.1)

In this Document
Purpose
Details
If this wait appears in the Top 5 waits, one can look to implement the following:
References
APPLIES TO:
Oracle Database - Enterprise Edition
Information in this document applies to any platform.
***Checked for relevance on 02-Sep-2016***
Database performance may get affected, inserts/DML might become slow.

PURPOSE
To understand enq: FB Contention, impact of this on Database Performance and how to resolve this contention.

DETAILS
enq: FB - contention is associated with block formatting. This enqueue is acquired to ensure only one process can format the blocks.

 

**If this wait appears in the Top 5 waits, one can look to implement the following:
1、Pre allocate the extents to the segment having a high number of inserts.
2、Look for any IO issue that is causing slowdown in formatting the blocks. One can see the average wait time for other IO waits like db file sequential read,log file parallel write etc. to see any generic IO issues.
3、Some time high IO operation like large index block split may require more extent allocation and may cause the enq: FB contention.
4、Avoid doing Multiple sessions doing the insert. Each session may try to format the block and can trigger the contention.
5、Check if any Parallel DML is happening; parallel slaves may try to format the blocks simultaneously.**

 

Bugs related to enq: FB contention:

Bug 6468758: STBH: USER SESSION BLOCKED BY ENQ: FB - CONTENTION
Bug 5557421: SELF-DEADLOCK OCCURS WHEN IMP SESSION IS KILLED
Bug 9239623: STBH: MMON IN CONTROL FILE SEQUENTIAL READ BLOCKING OTHER SESSIONS
BUG 9864080 - INSERT STATEMENT HAS SLOW PERFORMANCE WITH GC CR MULTIBLOCK REQUESTS; BB BUG 8735005
BUG 8735005 - SLOW INSERT - POSSIBLY DUE TO INDEX BLOCK SPLIT

因为在做压测，所以解决方法，预分配块即可。