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;

enq: TX - allocate ITL entry

相關文章