关于enq: TX - allocate ITL entry的问题分析(r3笔记第66天)

今天发现系统在下午1点左右的时候负载比较高,就抓取了一个最新的awr报告.

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

20892

26-Nov-14 13:20:17

3623

5.4

End Snap:

20893

26-Nov-14 13:30:17

3602

5.4

Elapsed:

10.01 (mins)

DB Time:

365.56 (mins)

来看看是否有top的等待事件,对于其它的几个等待事件都很熟悉了。有些问题早已进行了建议,还在修改中,但是对于第3个等待事件"enq: TX - allocate ITL entry"还是比较陌生的。准备来做一个深入的分析。

Event

Waits

Time(s)

Avg wait (ms)

% DB time

Wait Class

DB CPU

7,322

33.38

db file sequential read

3,833,628

5,031

1

22.94

User I/O

enq: TX - allocate ITL entry

187

3,607

19287

16.44

Configuration

direct path read temp

1,017,363

1,640

2

7.48

User I/O

read by other session

3,873,525

1,612

0

7.35

User I/O

因为得到的是最新的awr报告。所以就在sqllplus上简单排查了一把。可以看到有两个session都持有这个等待事件。

SQL>     select EVENT,sid,serial# from v$session where event='enq: TX - allocate ITL  entry';
EVENT                                                                    SID     SERIAL#
----------------------------------------------------------------  ---------- ----------
enq: TX - allocate ITL entry                                            5592      25063
enq: TX - allocate ITL entry                                           11533      51179

查看一下session的明细信息。可以看到这个session都是来自同一个客户端。

       SID    SERIAL#         USERNAME        OSUSER          MACHINE              PROCESS          TERMINAL        TYPE       LOGIN_TIME
---------- ----------  --------------- --------------- -------------------- ---------------  --------------- ---------- -------------------
      5592      25063   APPC          pappwrk01         prod_client_db              1234            unknown          USER       2014-11-26 13:03:45
     11533      51179   APPC           pappwrk01        prod_client_db              1234            unknown          USER       2014-11-26  13:03:46

查看这个session正在执行的sql语句,发现都是对同一个表的update。语句类似 UPDATE DIRECT_DEBIT_REQUEST SET DIRECT_DEBIT_REQUEST.ACCOUNT_ID=.......... 到这个地方,问题似乎很清晰了,就是由于表DIRECT_DEBIT_REQUEST的ITL的设置不能够满足并发事务的需求而导致的等待。数据块是oracle能够发出的最小I/O单位。在数据块中,数据块头部的ITL信息是至关重要的。 每当一个事务需要修改一个数据块时,需要在数据块头部获得一个可用的ITL槽,其中记录了当前事务的id,使用的undo数据块地址,还有对应的scn,事务是否提交等信息。如果一个新的事务发现ITL槽已经被使用,会重新 申请一个新的ITL槽,这个过程是动态的,进一步来说,ITL槽的设置是由ini_trans,max_trans来决定的,在10g之后,max_trans参数被忽略了。 SQL> create table trans_test(id number,name varchar2(100)) initrans 1 maxtrans 1; Table created. SQL> set linesize 100 SQL> col table_name format a30 SQL> select ini_trans,max_trans,table_name from user_tables where table_name='TRANS_TEST'; INI_TRANS MAX_TRANS TABLE_NAME ---------- ---------- ------------------------------ 1 255 TRANS_TEST

关于这个参数的解释在oracle sql reference中有详细的解释。

MAXTRANS Parameter

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.

对于initrans,maxtrans的默认值,表级为1,索引级为2. 一般来说不需要做特别的设置。可以根据业务的需要来配置。 来继续回到awr报告。可以在"Segments by ITL Waits"里面得到一个全面的信息。

Owner

Tablespace Name

Object Name

Subobject Name

Obj. Type

ITL Waits

% of Capture

APPO

INDXH01

MST_LOG_1IX

I2

INDEX PARTITION

12

66.67

APPO

DATAS01

DIRECT_DEBIT_REQUEST

TABLE

3

16.67

APPO

INDXS01

PAYMENT_2IX

A23_B2

INDEX PARTITION

1

5.56

APPO

INDXH01

ACTIVITY_HISTORY_1IX

C90

INDEX PARTITION

1

5.56

SYS

SYSAUX

WRH$_SQL_PLAN_PK

INDEX

1

5.56

从上面的列表我们可以清晰的看到对于ITL wait的情况,可以说明两点。 首先是我们在发现问题的时候,可能有些操作已经结束了,我们分析的结果不一定是最全面的信息。就如我们上面所做的分析。定位到问题的是表DIRECT_DEBIT_REQUEST,其实在问题时间段内,一个大的分区索引占用了高达 67%左右的ITL wait。 其次是我们通过awr报告得到了一些详细的信息。但是还是不能够对问题做最终的结论,不能头痛医头,脚痛医脚,如果能够以点带面来排查问题,可能最后的效率要高很多。 对于这个等待事件,处理的思路在metalink上也给出了详尽的解释。可以参见

Troubleshooting waits for 'enq: TX - allocate ITL entry' (Doc ID 1472175.1)

解决思路有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 <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 : B)

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

1) Set INITRANS to 50 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;

在这个基础上更近一步,我查找产品部的文档,发现在项目开始已经对initrans的设置做了建议,但是对于这个问题,不知道什么原因最后给漏掉了。这样下来需要对一些重要的表都需要做initrans的改动。本来从awr报告中得到的 的信息显示4个对象(表&索引)可能存在initrans设置不足的情况,如果结合项目的情况来看,需要做的变更可能就是上百个对象。需要好好评估。 以下的标准可以参考以下。 对于大表,数据千万级以上的表,initrans建议设置为8~16 对于中级表,数据量在百万到千万级,initrans建议设置为4~8 对于普通的表,initrans建议设置为1~4

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-11-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jerry的SAP技术分享

为什么CRM Opportunity的删除会触发一个通向BW系统的RFC

今天工作时我发现,我在SE38里用函数CRM_ORDER_DELETE删除一个Opportunity,居然弹出下图这个SAP Logon的屏幕,要连接BR1。这...

871
来自专栏杨建荣的学习笔记

通过shell绑定系统进程调优 (r4笔记第34天)

数据库的性能调优,需要基于操作系统的性能指标,如果操作系统级发生了一些状况,那么会潜移默化的影响到数据库层面。而数据库中对应的进程和操作系统级也有一定的映射关系...

3155
来自专栏更流畅、简洁的软件开发方式

细分主键

  主键本身是很简单的,但是围绕他产生的故事就不是那么简单了。 1、 管理 这个是最重要的,没有规矩不成方圆,主键要如何管理一定要实现确定好了,甚至有必要为此写...

2026
来自专栏杨建荣的学习笔记

ADG备库批量查询失败的原因分析(r8笔记第33天)

目前线上有一套环境是10gR2的,采用了一主两备的架构。在其中一个备库上每天凌晨会开放一个窗口运行一些批量的查询,目前使用dg broker会在指定的时间把备库...

3678
来自专栏杨建荣的学习笔记

关于dblink锁定带来的问题(r3笔记第20天)

可能在一些分布式环境中,有一些数据访问都需要用到db link。从某种程度上来说dblink是很方便,但是从性能上来说还是有一些的隐患。如果两个环境之间的网络情...

2975
来自专栏用户画像

mysql模拟题三

  9、找回mysql服务器root密码的很重要的一步是跳过权限表的检查启动mysql,该命令是(D)(2分)

1252
来自专栏杨建荣的学习笔记

备库报警邮件的分析案例(三)(r7笔记第16天)

继前两篇分析了一个看似非常普通的报警邮件,结果在分析问题的时候八面玲珑,相关因素都给分析了一下,没想到还真是有不小的收获。 前两篇地址: 备库报警邮件的分析案例...

3255
来自专栏杨建荣的学习笔记

动态创建MySQL Group Replication的节点(r11笔记第84天)

前几天分享了下搭建MySQL Group Replication的脚本, 分分钟搭建MySQL Group Replication测试环境(r11笔记第82天)...

3236
来自专栏杨建荣的学习笔记

一条简单的sql语句导致的系统问题(r4笔记第51天)

新年,给大家拜年了。祝大家工作顺利,万事如意。 今天照例简单检查了系统的情况,发现在客户的服务器在下午的3-5点这个时间段,数据库负载略有上升,但是幅度不大,因...

3696
来自专栏杨建荣的学习笔记

物化视图中的统计信息导致的查询问题分析和修复 (r7笔记第47天)

今天开发的同事下午反馈给我一个问题,说有操作直接卡住了,听这个描述,感觉很可能是查询慢了。 于是连接到环境中,查看了一下正在执行的sql语句情况,发现下面的语句...

3515

扫码关注云+社区

领取腾讯云代金券