當表的ITL設定不能滿足併發事務的需求時會產生此等待。資料塊是oracle能夠發出的最小i/o單位。在資料塊中，資料塊每當一個事務需要修改一個資料塊時，需要在資料塊頭部獲得一個可用的ITL槽，其中記錄了當前事務的id，使用的undo資料塊，還有對應的scn，事務是否提交等資訊。進一步來說ITL槽的設定是由ini_trans，max_trans決定。在10g之後，nax_trans引數被忽略了。
SQL> create table trans_test(id number,name varchar2(100)) initrans 1 maxtrans 1;
Table created
SQL> select ini_trans,max_trans,table_name from user_tables a where a.table_name='TRANS_TEST';
INI_TRANS MAX_TRANS TABLE_NAME
---------- ---------- ------------------------------
1 255 TRANS_TEST

In earlier releases, the MAXTRANS parameter determined the maximum number of concurrent update transactions allowed for each data block in the segment. This parameter has been deprecated. Oracle now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.

Existing objects for which a value of MAXTRANS has already been set retain that setting. However, if you attempt to change the value for MAXTRANS, Oracle ignores the new specification and substitutes the value 255 without returning an error.

對於ini_trans, max_trans的預設值，表級為1,索引級為2. 一般來說不需要做特別的設定。可以根據業務的需要來配置。

以下設定可供參考：
對於大表，資料千萬級以上的表，initrans建議設定為8~16
對於中級表，資料量在百萬到千萬級，initrans建議設定為4~8
對於普通的表，initrans建議設定為1~4

對於此等待事件，解決思路有三種：

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 INITRANS 50;

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

3) Then rebuild all the indexes of this table as below

alter index 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 :

1) Spreading rows into more number of blocks will also helps to reduce this wait event.

alter table 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

1) Set INITRANS to 50  pct_free to 40

alter table PCTFREE 40  INITRANS 50;

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

3) Then rebuild all the indexes of the table as below

alter index  rebuild PCTFREE 40 INITRANS 50;

關於enq: TX - allocate ITL entry等待事件

Increase INITRANS

Increase PCTFREE

A Combination of increasing both INITRANS and PCTFREE

相關文章