关于enq:TX-allocateITLentry等待事件

当表的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-allocateITLentry等待事件
本文URL：http://myzitong.com/article/iieejj.html

关于enq:TX-allocateITLentry等待事件

Increase INITRANS

Increase PCTFREE

A Combination of increasing both INITRANS and PCTFREE

其他资讯