【故障处理】队列等待之TX - allocate ITL entry案例


【故障处理】队列等待之TX - allocate ITL entry案例

 BLOG文档结构图

 

wps9D6B.tmp 

 

 前言部分

2.1  导读和注意事项

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

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

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

③ 队列等待的基本知识

Tips:

① 本文在ITpubhttp://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和微信公众号(xiaomaimiaolhr)有同步更新

② 文章中用到的所有代码,相关软件,相关资料请前往小麦苗的云盘下载(http://blog.itpub.net/26736162/viewspace-1624453/

③ 若文章代码格式有错乱,推荐使用搜狗360或QQ浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://blog.itpub.net/26736162/viewspace-1624453/,另外itpub格式显示有问题,可以去博客园地址阅读

④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体注;对代码或代码输出部分的注释一般采用蓝色字体表示

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

[ZHLHRDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZHLHRDB1:root]:/>

00:27:22 SQL> alter tablespace idxtbs read write;

 

====》2097152*512/1024/1024/1024=1G 

 

本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。

 

 

 故障分析及解决过程

3.1  故障环境介绍

 

项目

Source db

db 类型

RAC

db version

11.2.0.3.0

db 存储

ASM

OS版本及kernel版本

AIX 64位 7.1.0.0

 

3.2  故障发生现象及报错信息

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

wps9D7B.tmp 

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

wps9D7C.tmp 

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

wps9D7D.tmp 

等待事件很明显了:

wps9D8E.tmp 

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

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,pctfree10,那么如果是这样一种情况,如果表中经常执行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;

 

3.3  故障分析及解决

有了以上的知识,我们知道,目前首先需要找到产生等待事件的表,然后修改INITRANS和PCTFREE来重构表就可以了。

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

wps9D8F.tmp 

wps9D90.tmp 

 

wps9D91.tmp 

 

 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#;

wps9DA2.tmp 

 

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

wps9DA3.tmp 

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

wps9DA4.tmp 

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

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

wps9DA5.tmp 

pct_free为10ini_trans1,我们根据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索引才可以。

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

 

wps9DA6.tmp 

 

  About Me

..........................................................................................................................................................................................................                        

 本文作者:小麦苗,只专注于数据库的技术,更注重技术的运用

 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新,推荐pdf文件阅读

 QQ群:230161599 微信群:私聊

 本文itpub地址:http://blog.itpub.net/26736162/viewspace-2124735/  博客园地址:http://www.cnblogs.com/lhrbest/articles/5854407.html

 本文pdf版:http://yunpan.cn/cdEQedhCs2kFz (提取码:ed9b)

 小麦苗分享的其它资料:http://blog.itpub.net/26736162/viewspace-1624453/

 联系我请加QQ好友(642808185),注明添加缘由

  2016-09-06 09:00~2016-09-06 19:00 在中行完成

 【版权所有,文章允许转载,但须以链接方式注明源地址,否则追究法律责任】

..........................................................................................................................................................................................................

长按识别二维码或微信客户端扫描下边的二维码来关注小麦苗的微信公众号:xiaomaimiaolhr,学习最实用的数据库技术。

wps9DB6.tmp

 



本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
Oracle 一次缩小表空间的处理过程 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  收缩表空间的几种办法 ② 表空间大小查询 ③  AIX 下查询磁盘空间大小的 shell 脚本 ④ 删除数据文件的正确方法 ⑤  ORA-03262 处理 ⑥ 缩小数据文件 ⑦  su - grid asmcmd lsdg 的使用 ⑧ 其他常用命令   Ti
等待事件系列(1)--User I/O类型(上) 全文可参考: http://www.cnblogs.com/lhrbest/articles/5835420.html   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  等待事件系列(1)--User I/O类型     Tips: ① 本文在 ITpub ( http://blog.itpu
等待事件系列(1) --User I/O 类型(下) 全文可参考: http://www.cnblogs.com/lhrbest/articles/5835420.html   1    BLOG 文档结构图   6.3      d b file parallel read SELECT   * FROM    v$event_name WHERE    NAME   IN   ( 'db file parallel read' );   在 V$SESSION_WAIT这个视图里面,这个等待事件有三个

mysql存储过程小试牛刀 - 2016-09-03 14:09:13

(1). 格式 MySQL 存储过程创建的格式: CREATE PROCEDURE 过程名 ([ 过程参数 [,...]]) [ 特性 ...] 过程体 这里先举个例子: mysql DELIMITER //   mysql CREATE PROCEDURE proc1(OUT s int)     - BEGIN      - SELECT COUNT(*) INTO s FROM user;       - END      - //   mysql DELIMITER ;    注: ( 1 )这里
【故障处理】队列等待之 enq: TX - row lock contention 1    BLOG 文档结构图   2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: TX - row lock contention 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 ④  ADDM 的使用 ⑤ 如何获取历史执行计划 ⑥ 查询绑定变量的具体值 ⑦ 很多有用的查询性
       ORA-01408:such column list already indexed问题的原因是当新建一个索引时,索引对应的字段和字段顺序和已经存在的索引相同。        最近一个需求,是将主键索引转成HASH分区的主键索引,需要新建一个索引,然后将现有的主键索引去掉。如果直接新建一个字段相同且字段顺序相同的索引,就会报ORA-01408。当然,可以将现有的主键索引先删掉,但这样在新索引建立之前,无法保证数据的唯一性,并且按主键的更新删除操作会因为无索引而变的很慢。        我们可
继续上次分析的一个问题,一个简单的SQL语句执行计划有些奇怪,明明可以走唯一性索引但是却走了另外一个索引。 当然了,最后逐步定位,发现是在直方图的地方有一些差别。取消直方图之后,执行计划立刻恢复了正常。 当然问题来了,这个是为什么呢,收集统计信息中的auto选项是什么含义呢。为什么两个数据类型一样的(varchar2(64))的列,境遇却大大不同。 我们来看看一些统计信息的数据。 为了跟进一步验证数据的分布律和选取代价,我们查询它的直方图信息。 SQL   select to_char(endpoint_v
首先说明_fairness_threshold参数仅在RAC环境下才有意义,让我对这个参数引起关注是在某一次查询v$cr_block_server时,发现该视图有一个名为FAIRNESS_DOWN_CONVERTS的字段,官方文档里将其解释为:Number of times an instance receiving a request has down-converted an X lock on a block because it was not modifying the block 该参数相关性
    今天看到有一个网友提了一个问题,描述很简短     测试DG时,主库不能宕机,如何测试failover?     其实这个需求从业务层面来说是合理的,一个数据量很大的核心数据库,如果需要做灾难演练,就希望在备库上做一下演练工作,而这个演练其实又不想影响到目前的主库,而且又希望能够尽可能模拟真实的情况,我想这样对于运维部门来说是最具有考核力度,而对于开发业务部门来说是最受欢迎的,因为他们什么都不需要改动。 而从技术角度来看,似乎有一些地方需要考量,如果备库Failover为主库,那么这个主库肯定是可以

简单分析percona-zabbix-templates - 2016-09-01 04:09:15

    当Zabbix和Percona两者相遇,会擦出不少的开源火花来,众人拾柴火焰高,最终受益的还是大部分运维人员。     我很早就用过Percona提供的MySQL监控模板,但是却没有刨根问底,只是简单使用而已,自从定制了Orabbix之后,我还是信心满满,MySQL的数据字典相对要少很多,监控起来可能想必Oracle要少很多,不过关于Percona的这个插件,我还是带着好奇之心,内部是否有很多独门秘籍,我想好好学学那些监控项对应的SQL,好好弥补我对于MySQL监控的一些空缺,所以简单分析这个模板就