Oracle 10.2.0.4:
We are continuously being hit by "enq: TX - allocate ITL entry" on one of the tables. I am trying to understand why is that even after increasing initrans to 100 and MAXTRANS of 255 isn't helping. We get around 100 inserts per sec in this table and around 100 updates on this table.
This table has 2 blob columns. One of the blob colums raw_p (100k) gets updated during insert and then during update proc_p (100 bytes) gets updated. I've also tried increasing PCTFREE to 50 but that didn't help either. This table is hash partitioned in 32 partitions on P_ID which is a sequence. Block size is 8K. LOB tablspace block size is 32K. For both LOB in-line storage is enabled.
This table is something like this:
Name Null? Type
----------------------------------------- -------- ----------------------------
P_ID NOT NULL NUMBER(19)
F_ID NOT NULL NUMBER(19)
P_NAME NOT NULL VARCHAR2(255)
P_TYPE NOT NULL NUMBER(10)
R_RAW_PR_ID NUMBER(19)
RAW_P BLOB -- This is always 100K+ So which means stores a Index pointer
PROC_P BLOB -- Null when inserted and then get updated later. One update for every insert. This is 100bytes