关于enq: TX - allocate ITL entry等待事件

原创 作者:kakaxi9521 时间:2018-07-05 10:58:43 0 删除 编辑
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';
---------- ---------- ------------------------------
         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.  一般来说不需要做特别的设置。可以根据业务的需要来配置。




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;

下一篇: 没有了~
请登录后发表评论 登录


  • 博文量
  • 访问量