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

Sorry for typing too fast.

Martin

Martin Klier schrieb:
> Hi Thomas,
>
> 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