Re: High value of "enq: TX - allocate ITL entry" waits but wait events are on an index not affected by the SQL statement
- From: Martin Klier <usn@xxxxxxxxx>
- To: thomas.kellerer@xxxxxxxxxx
- Date: Mon, 22 Jul 2013 17:02:41 +0200
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
