While using insert /*+ append */ to insert into a 11gr2 database, I found 80% of the waits being enq: HV - contention .
HV enqueue – From the description of event which says Lock used to broker the high watermark during parallel inserts, you would assume that we have to tweak the insert statement .
Solution:
After few tests, the source of the problem was found to be the underlying datafile extending, The files were extending by only 4M. I resized the datafile to a size i expect the data to grow.
alter database datafile '/filename' resize xxG;
The wait event completely disappeared and the insert was 3 times faster.
