SQL> l
1* select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
SQL> /
Enter value for event: enq: TX - allocate ITL entry
old   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'enq: TX - allocate ITL entry'

NAME                           PARAMETER1      PARAMETER2      PARAMETER3
------------------------------ --------------- --------------- ---------------
enq: TX - allocate ITL entry   name|mode       usn<<16 | slot sequence

SQL>

原因；
By default the INITRANS value for a table is one, and for an index is two. This defines an internal block structure called the Interested Transaction List (ITL). In order to modify data in a block, a process needs to use an empty ITL slot to record that the transaction is interested in modifying some of the data in the block. If there are insufficient free ITL slots then new ones will be taken in the free space reserved in the block. If this runs out and too many concurrent DML transactions are competing for the same data block we observe contention against the following wait event - "enq: TX - allocate ITL entry".
默认情况下，表的INITRANS值是1，索引是2。这定义了一个名为Interested Transaction List（ITL）的内部块结构。为了修改块中的数据，进程需要使用一个空的ITL槽来记录事务有兴趣修改块中的一些数据。如果没有足够的免费ITL插槽，那么将在该块中保留的空闲空间中使用新插槽。如果这种情况发生，而且有太多的并发DML事务处理竞争同一个数据块，我们就会观察到下列等待事件 - "enq：TX - 分配ITL条目"的争用。

排查：
检查awr的Segments by ITL Waits部分

解决：
此问题的主要解决方案是通过重新创建表或索引来增加ITL容量，并更改INITRANS或PCTFREE参数以处理更多并发事务。这反过来将有助于减少"enq：TX - 分配ITL条目"的等待事件。

Increase INITRANS

1) Depending on the number of transactions in the table we need to alter the value of INITRANS. here it has been changed to 50:
alter table <table name> INITRANS 50;

2) Then re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of this table as below
alter index <index_name> rebuild INITRANS 50;

Increase PCTFREE

If the issue is not resolved by increasing INITRANS then try increasing PCTFREE. Increasing PCTFREE holds more space back and so spreads the same number of rows over more blocks. This means that there are more ITL slots available, overall.如果问题没有通过增加INITRANS来解决，那么尝试增加PCTFREE。增加PCTFREE会占用更多的空间，因此会在更多的块上传播相同数量的行。这意味着总体上有更多的ITL插槽可用。

1) Spreading rows into more number of blocks will also helps to reduce this wait event.
alter table <table name> PCTFREE 40;

2) Then re-organize the table using move (alter table service_T move;)

3) Rebuild index
alter index index_name rebuild PCTFREE 40;

A Combination of increasing both INITRANS and PCTFREE(增加INITRANS和PCTFREE的结合)

1) Set INITRANS to 50 and pct_free to 40
alter table <table_name> PCTFREE 40 INITRANS 50;

2) Re-organize the table using move (alter table <table_name> move;)

3) Then rebuild all the indexes of the table as below
alter index <index_name> rebuild PCTFREE 40 INITRANS 50;

ITPub博客

enq: TX - allocate ITL entry

最新文章