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;
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;