Wait Event enq: TX – allocate ITL entry

Oracle Version:
Platform: Windows Server 2003, SP2

Our system had steadily been getting busier and busier, as the business grew and more customers start using the system. Up until now the system has been performing quite well; the AWR reports don’t show any unwanted wait events and the SQL queries have been tuned nicely by the development team.

Just yesterday, however, we saw a large spike on our OEM monitoring graph which we have up on a large set of monitoring screens, the action count dropped but then picked back up again as the spike under the configuration category in the Enterprise Manager Top Activity section went back to normal.

Upon further inspection we noticed that the spike was in both the configuration and concurrency categories. In particular there were waits on the “enq: TX – allocate ITL entry” wait event and also index contention.

The statement which showed as being hit by this contention was a very simple insert statement against one of our most heavily used tables. This is a table which stores the information about every transaction which occurs on the system. The table uses a sequence to generate the primary key value and a couple of other indexes on it to allow faster searching.

After some debate the theory was that there was contention on the primary key index blocks because of the monotonically increasing sequence number and the very high number of insert statements being run against that table, and this was the cause of the “enq: TX – allocate ITL entry” wait event which we were seeing.

A quick search on the internet and Metalink showed that this could be a problem when there are hot blocks, usually when block splits occur and other sessions have to wait for this to happen. This is quite often the case where you have a very high insert rate on monotonically increasing number, such as a primary key. It should also be noted that this is more likely to occur in RAC systems, due to the higher throughput (usually) when compared to a single instance and the amount of transferring of the hot block between caches which will need to happen.

It’s possible to use this query to check out potential segments which have high waits on the “enq: TX – allocate ITL entry” wait event:

SELECT owner,object_name,tablespace_name,object_type,statistic_name,value
FROM v$segment_statistics
WHERE statistic_name = ‘ITL waits’
AND value > 500
ORDER BY value desc;

Further information can be seen in Metalink Article: 1373500.1.

The Solution

One option is to use reverse key index so that the sequence numbers are no longer always trying to be inserted into the furthest right hand block which is what can cause the contention. I won’t go into any more detail about reverse key indexes because a very quick Google search shows many well described documents and the Oracle documentation explains it well, also.

At first we attempted to increase the initrans value for the index but this only appeared to make it happen more frequently. One theory was that by increasing the number of transactions which could be made against the index block at any one time meant that there was more contention placed upon the hot block.

We started to test out using reverse key indexes but in the meantime we had an 11g upgrade planned so we rolled that out anyway and the contention seemed to go away. Perhaps there was a change in the database in version 11 which helped to reduce the contention that we were seeing. We’re still carrying out the testing though, as there is definitely some contention there and if we can use a reverse key index without any kind of penalty then this should hopefully free up some resource and improve performance.

I hope that this has helped you in some way, or just given you some thoughtful information

Like it, share it...

Category: Indexes

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *