LOB Segments and enq: HW Contention

If you have LOB segments and are experiencing system wait event 'enq: HW Contention' then you should use this workaround:

1-Check for HW contention:
select
*
from
gv$session_wait
where
event like '%contention%';

2-Get the block and file id
select
dbms_utility.data_block_address_file(id2) file#,
dbms_utility.data_block_address_block(id2) block#
from
gv$lock
where
type='HW'
and
inst_id= (&use the instance number where you found the HW contention);

3-Find the object
select
owner,
segment_type,
segment_name
from
dba_extents
where
file_id=&USE_THE_FILE#
and
&USE_BLOCK_NUMBER between block_id
and
block_id + blocks - 1;

4-Check the extent size and add one extent:
alter table owner.table_name
modify lob (lob_name) (allocate extent (size 131072k));


5-Your HW Contention have gone. I hope so:)