Wednesday, December 25, 2013

enq: FB - contention

Was working on splitting a partition from max (about 300 Million Records) to month wise and happened to notice this wait event.

What is this event means?
This is associated with block formatting and it ensures that only one process format the blocks in an ASSM tablespace.
As I mentioned earlier, I was splitting a partition with 8 parallels and each sessions doing insert and could be that each session tried to format the block which caused the contention.
Below query will give you the details
select session_id, sql_id, event, p1, p1text, p2 "TS#", p3 "Block", wait_time, session_state, time_waited
  from gv$active_session_history
 where event like '%FB%'
   and sql_id = 'dg8ksy7wvv1nd'; --replace sqlid here

Query v$tablespace for the returned TS# to find out which tablespace.
Use below query to find the block and database file associated with it.

select dbms_utility.data_block_address_block(p3 value from above query) "BLOCK",
         dbms_utility.data_block_address_file(p3 value from above query) "FILE"
  from dual;

No comments:

Post a Comment