Oracle 等待事件队列等待之TX - allocate ITL entry案例

0    56    1

👉 本文共约3480个字,系统预计阅读时间或需14分钟。

前言部分

导读和注意事项

各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:

① enq: TX - allocate ITL entry等待事件的解决

② 一般等待事件的解决办法

③ 队列等待的基本知识

故障分析及解决过程

故障环境介绍

项目Source db
db 类型RAC
db version11.2.0.3.0
db 存储ASM
OS版本及kernel版本AIX 64位 7.1.0.0

故障发生现象及报错信息

最近事情比较多,不过还好,碰到的都是等待事件相关的,同事发了个AWR报告,说是系统响应很慢,我简单看了下,简单分析下吧:

Oracle 等待事件队列等待之TX - allocate ITL entry案例

20分钟时间而DB Time为11461分钟,DB Time太高了,负载很大,很可能有异常的等待事件,系统配置还是比较牛逼的。

Oracle 等待事件队列等待之TX - allocate ITL entry案例

事务量很大,其它个别参数有点问题,不一一解说了。Instance Efficiency Percentages也有点问题:

Oracle 等待事件队列等待之TX - allocate ITL entry案例

等待事件很明显了:

Oracle 等待事件队列等待之TX - allocate ITL entry案例

AWR的其它部分就不分析了,首先这个等待事件:enq: TX - allocate ITL entry比较少见,查了一下MOS,有点收获:Troubleshooting waits for 'enq: TX - allocate ITL entry' (文档 ID 1472175.1)

Observe high waits for event enq: TX - allocate ITL entry

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class

enq: TX - allocate ITL entry 1,200 3,129 2607 85.22 Configuration

DB CPU 323 8.79

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

gc buffer busy acquire 17,261 50 3 1.37 Cluster

gc cr block 2-way 143,108 48 0 1.32 Cluster

gc current block busy 10,631 46 4 1.24 Cluster

CAUSE

By default INITRANS value for table is 1 and for index is 2. This defines an internal block structure called the Interested Transaction List (ITL). In order to modify data in a block, a process needs to use an empty ITL slot to record that the transaction is interested in modifying some of the data in the block. If there are insufficient free ITL slots then new ones will be taken in the free space reserved in the block. If this runs out and too many concurrent DML transactions are competing for the same data block we observe contention against the following wait event - "enq: TX - allocate ITL entry".

You can see candidates for re-organisation due to ITL problems in the "Segments by ITL Waits" section of an Automatic Workload Repository (AWR) report:

Segments by ITL Waits

* % of Capture shows % of ITL waits for each top segment compared

* with total ITL waits for all segments captured by the Snapshot

Owner Tablespace Name Object Name Subobject Name Obj. Type ITL Waits % of Capture

PIN BRM_TABLES SERVICE_T TABLE 188 84.30

PIN BRM_TABLES BILLINFO_T P_R_06202012 TABLE PARTITION 35 15.70

SOLUTION

The main solution to this issue is to increase the ITL capability of the table or index by re-creating it and altering the INITRANS or PCTFREE parameter to be able to handle more concurrent transactions. This in turn will help to reduce "enq: TX - allocate ITL entry" wait events.

To reduce enq: TX - allocate ITL entry" wait events, We need to follow the steps below:

1) Set INITRANS to 50 and 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值不合适,引起的事务槽分配等待。当一个事务需要修改一个数据块时,需要在数据块头部获取一个可用的ITL槽,用于记录事务的id,使用undo数据块地址,scn等信息。如果事务申请不到新的可用ITL槽时,就会产生enq: TX - allocate ITL entry等待。 发生这个等待时,要么是块上的已分配ITL个数(通过ini_trans参数控制)达到了上限255(10g以后没有了max_trans限制参数,无法指定小于255的值),要么是这个块中没有更多的空闲空间来容纳一个ITL了(每个ITL占用24bytes)。 默认情况下创建的表ITL槽数最小为1+1,pctfree为10,那么如果是这样一种情况,如果表中经常执行update语句,然后块中剩余的10%空间所剩无几,而且业务的并发量还很大,此时就很容易遇到enq: TX - allocate ITL entry等待。

解决:解决方式就是调整表和索引的INITRANS,有必要还需要调整pcfree值。

1) Set INITRANS to 50 and 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和PCTFREE来重构表就可以了。

我们查看AWR中的Segments by ITL Waits部分:

Oracle 等待事件队列等待之TX - allocate ITL entry案例

Oracle 等待事件队列等待之TX - allocate ITL entry案例

Oracle 等待事件队列等待之TX - allocate ITL entry案例

SELECT D.SQL_ID,

CHR(BITAND(P1, -16777216) / 16777215) ||

CHR(BITAND(P1, 16711680) / 65535) "Lock",

BITAND(P1, 65535) "Mode",

D.CURRENT_OBJ#,

COUNT(1),

COUNT(DISTINCT D.SESSION_ID)

FROM DBA_HIST_ACTIVE_SESS_HISTORY D

WHERE D.SAMPLE_TIME BETWEEN

TO_DATE('2016-09-05 16:55:00', 'YYYY-MM-DD HH24:MI:SS') AND

TO_DATE('2016-09-05 17:15:00', 'YYYY-MM-DD HH24:MI:SS')

AND D.EVENT = 'enq: TX - allocate ITL entry'

GROUP BY D.SQL_ID,

(CHR(BITAND(P1, -16777216) / 16777215) ||

CHR(BITAND(P1, 16711680) / 65535)),

(BITAND(P1, 65535)),

D.CURRENT_OBJ#;

Oracle 等待事件队列等待之TX - allocate ITL entry案例

SELECT * FROM v$sql a WHERE a.SQL_ID='1cmnjddakrqbv';

Oracle 等待事件队列等待之TX - allocate ITL entry案例

SELECT * FROM Dba_Objects d WHERE d.object_id=87620;

Oracle 等待事件队列等待之TX - allocate ITL entry案例

好吧,知道了表名,我们查看一下表的属性:

SELECT * FROM Dba_Tables d WHERE d.table_name='ORGANIZATION';

Oracle 等待事件队列等待之TX - allocate ITL entry案例

pct_free为10,ini_trans为1,我们根据MOS应该修改这2个值,SQL如下:

ALTER TABLE ORGANIZATION PCTFREE 20 INITRANS 16;

ALTER TABLE ORGANIZATION MOVE;

ALTER INDEX PK_ORGANIZATION REBUILD PCTFREE 20 INITRANS 16 NOLOGGING;

这里需要注意:该表大约2000条记录,很小,所以MOVE的时候可以不用并行,也不用NOLOGGING,若是表很大的时候可以考虑并行+NOLOGGING属性,另外,还需要REBUILD索引才可以。

修改完成后,开发人员经过测试后终于可以了,给我简单回复了一下。

Oracle 等待事件队列等待之TX - allocate ITL entry案例

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

5 × 2 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部