Re: High value of "enq: TX - allocate ITL entry" waits but wait events are on an index not affected by the SQL statement

to explain this behavior, we should know a little bit more about the transactions happening.

ITL entries are maintained on block level and work for row level OF A TABLE, so you can get locked by updating the same row - independent of the columns you are touching.

The wait event "enq: TX - allocate ITL entry" means, that you have more open transactions then you have free ITL slots there. So a new slot has to be created. After this, the locking mechanism can start doing its job.

You can try to
select statistics_name, value
from v$segment_statistics
where object_name in (table, index1, index2, index3); to find out more stuff about this issue in real time.

To answer your final question: I think increasing the space for the block header can help, but I'd also have a look at the number of transactions open at one time.

HTH Regards
Martin

Thomas Kellerer schrieb:

> What I don't understand is the fact that the UPDATE statement does not touch any of the columns from ORDERS_IDX1 index. The second statement (bgy3mh3wby1js) is an insert statement into the ORDERS table which naturally would touch the index ORDERS_IDX1 (and all the others).
>
> Could that be caused by index block splits due to increasing in the ITL?
> The wait count is also way higher than the actual execution count of 1019 the UPDATE statement in the time frame in question.

-- 
Usn's IT Blog for Oracle and Linux
http://www.usn-it.de

--
http://www.freelists.org/webpage/oracle-l