What causes high enq: HW - Contention when ASSM is used?
We have a table with no LOBS that has ASSM turned on. This morning we had very high waits on "enq: HW - Contention" with multiple insert statements. This is a very large table with billions of rows. There are 252 datafiles in the tablespace. All files have autoextend turned on. The last ten or so files have space left. The rest are full. The insert sessions all appeared to be waiting for space on the last datafile. Here is the definition of the table.
CREATE TABLE "COMS"."AUD_TRX_ARCHIVE_DTL2"
( "ARCH_ID" NUMBER(35,0) NOT NULL ENABLE,
"SEQ_ID" NUMBER(12,0) NOT NULL ENABLE,