Friday, October 1, 2010

HV enqueue – contention during parallel inserts.

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 .

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.