Oracle AWR之-enq: TX - allocate ITL entry-程序员宅基地

今天收到压力测试期间awr报告，测试人员要我看看数据库是否有可以优化的地方，数据库服务器配置信息：CPU:32*8,内存：480g 单实例数据库：oracle 11.2.0.4。具体分析过程如下：

可以发现，压力测试期间出现队列锁：enq: TX - allocate ITL entry。

通过Segments by ITL Waits发现等待的对象为hqoa_t_busi和hqoa_t_sendfile两张表：

通过sql执行时间发现了关于对象hqoa_t_busi表的dml语句，基本可以确定问题是由于压力测试期间并发大量对于该对象的update语句导致的数据库问题。

到这个地方，问题似乎很清晰了，就是由于表DIRECT_DEBIT_REQUEST的ITL的设置不能够满足并发事务的需求而导致的等待。数据块是oracle能够发出的最小I/O单位。在数据块中，数据块头部的ITL信息是至关重要的。
每当一个事务需要修改一个数据块时，需要在数据块头部获得一个可用的ITL槽，其中记录了当前事务的id，使用的undo数据块地址，还有对应的scn,事务是否提交等信息。如果一个新的事务发现ITL槽已经被使用，会重新
申请一个新的ITL槽，这个过程是动态的，进一步来说，ITL槽的设置是由ini_trans,max_trans来决定的，在10g之后，max_trans参数被忽略了。

对于initrans,maxtrans的默认值，表级为1,索引级为2.  一般来说不需要做特别的设置。可以根据业务的需要来配置。

通过查询相关信息，确定enq: TX - allocate ITL entry等待事件解决办法如下：

解决思路有3种

Increase INITRANS

A)

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 :
B)

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;

对于大表，数据千万级以上的表，initrans建议设置为8~16
对于中级表，数据量在百万到千万级，initrans建议设置为4~8
对于普通的表，initrans建议设置为1~4

解决压力测试期间问题方法如下：

--update HQOA_T_BUSI set busiId= :1 , title= :2 where busiId = :3;

altertable HQOA_T_BUSI pctfree20INITRANS8;

altertable HQOA_T_BUSI move;

alterindex idx_HQOA_T_BUSI rebuildPCTFREE20INITRANS16;

begin

dbms_stats.gather_table_stats(ownname =>'OA_36',

tabname =>'HQOA_T_BUSI',

estimate_percent =>100,

cascade=>true,

degree=>20);

END;

select*from HQOA_T_BUSI;

selectcount(*)from HQOA_T_BUSI;

重新进行压力测试发现：

同样方法处理hqoa_t_sendfile对象后，发现数据库性能正常，而且压力测试也上去了。