【故障处理】队列等待之TX - allocate ITL entry引起的死锁处理

【故障处理】队列等待之TX - allocate ITL entry引起的死锁处理

 BLOG文档结构图

wpsA830.tmp 

 

 

 前言部分

2.1  导读和注意事项

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

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

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

③ 队列等待的基本知识

④ ITL死锁解决

⑤ ITL死锁模拟

⑥ Merge语句的非关联形式的查询优化

  Tips:

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

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

③ 网页文章代码格式有错乱,推荐使用360浏览器,也可以下载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_XLHRD_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  故障发生现象及故障分析解决

早上刚来上班,同事就发了一个SQL过来,说是有锁,然后我就查了查系统里的锁,结果一个锁都没得。好吧,还是得干点事的,先看看SQL语句:

MERGE INTO TLHR.TLHRBOKBAL S

USING (SELECT A.BOOKACCOUNT AS BOOKACCOUNT,

              (A.CURRBALANCE + NVL(B.BAL, 0.00)) AS BANKAMT

         FROM TLHR.TLHRBOKBAL_TMP A,

              (SELECT T1.BOOKACCOUNT AS BOOKACCOUNT,

                      SUM(DECODE(T1.DCFLAG, 'D', -T1.AMT, 'C', T1.AMT, 0)) AS BAL

                 FROM TLHR.TLHRBOKBALJN T1

                WHERE T1.BOOKACCOUNT LIKE '13500000%'

                  AND T1.TRANDATE = '20150901'

                  AND (T1.REASON IN ('1', '2') OR

                      (T1.REASON = '0' AND T1.ONLINEFLAG = '1'))

                GROUP BY T1.BOOKACCOUNT) B

        WHERE A.BOOKACCOUNT = B.BOOKACCOUNT(+)

          AND A.BOOKACCOUNT LIKE '13500000%') T

ON (S.BOOKACCOUNT = T.BOOKACCOUNT)

WHEN MATCHED THEN

  UPDATE

     SET S.LASTBALANCE = T.BANKAMT,

         S.CURRBALANCE = T.BANKAMT,

         S.DEBITAMT    = 0.00,

         S.CREDITAMT   = 0.00;

看起来是一个MERGE语句,按照小麦苗以前的经验,这一类的SQL最好是修改为MERGE的非关联形式比较好,我们先看看执行计划有没有问题:

先找到SQL_ID53qv858pwwwwb

SELECT a.ELAPSED_TIME,a.EXECUTIONS,a.* FROM v$sql a WHERE a.SQL_TEXT LIKE '%MERGE INTO TLHRBOKBAL S%' AND A.SQL_TEXT LIKE '%13500000%'  ;

查询历史执行计划:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID => '53qv858pwwwwb' )) ;

Plan hash value: 2695089823

 

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

| Id  | Operation                        | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | MERGE STATEMENT                  |                 |       |       |       |   155K(100)|          |

|   1 |  MERGE                           | TLHRBOKBAL      |       |       |       |            |          |

|   2 |   VIEW                           |                 |       |       |       |            |          |

|   3 |    HASH JOIN RIGHT OUTER         |                 |   153K|    15M|       |   155K  (2)| 00:31:04 |

|   4 |     VIEW                         |                 |     1 |    31 |       |     6   (0)| 00:00:01 |

|   5 |      SORT GROUP BY               |                 |     1 |    41 |       |     6   (0)| 00:00:01 |

|   6 |       TABLE ACCESS BY INDEX ROWID| TLHRBOKBALJN    |     1 |    41 |       |     6   (0)| 00:00:01 |

|   7 |        INDEX RANGE SCAN          | PK_TLHRBOKBALJN |     2 |       |       |     4   (0)| 00:00:01 |

|   8 |     HASH JOIN                    |                 |   153K|    10M|  5848K|   155K  (2)| 00:31:04 |

|   9 |      TABLE ACCESS FULL           | TLHRBOKBAL_TMP  |   153K|  4048K|       | 85415   (2)| 00:17:05 |

|  10 |      TABLE ACCESS FULL           | TLHRBOKBAL      |   305K|    13M|       | 68755   (3)| 00:13:46 |

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

可以看到,该执行计划的顺序为【7-->6-->5-->4-->9-->10-->8-->3-->2-->1-->0】,而耗费性能的地方在91083个步骤上,走的是全表扫描,我们先看看2个大表的数据量:

SELECT COUNT(*)  FROM TLHR.TLHRBOKBAL_TMP A WHERE  A.BOOKACCOUNT LIKE '13500000%';   --306043/38998765

SELECT COUNT(*) FROM TLHR.TLHRBOKBAL A WHERE  A.BOOKACCOUNT LIKE '13500000%';    --306043/38826275

3000万的数据里边取出30万的数据,还是比较少的,所以应该去走索引的,看了一下统计信息,也是最新收集的,好吧,算了,先修改一下SQL让其走索引扫描看看,

MERGE  INTO TLHR.TLHRBOKBAL S

USING (SELECT S.ROWID ROWIDS,

              A.BOOKACCOUNT AS BOOKACCOUNT,

              (A.CURRBALANCE + NVL(B.BAL, 0.00)) AS BANKAMT

         FROM (SELECT /*+index(NB,PK_TLHRBOKBAL_TMP)*/NB.CURRBALANCE,NB.BOOKACCOUNT

                 FROM TLHR.TLHRBOKBAL_TMP NB

                WHERE NB.BOOKACCOUNT LIKE '13500000%') A,

              TLHR.TLHRBOKBAL S,

              (SELECT T1.BOOKACCOUNT AS BOOKACCOUNT,

                      SUM(DECODE(T1.DCFLAG, 'D', -T1.AMT, 'C', T1.AMT, 0)) AS BAL

                 FROM TLHR.TLHRBOKBALJN T1

                WHERE T1.BOOKACCOUNT LIKE '13500000%'

                  AND T1.TRANDATE = '20150901'

                  AND (T1.REASON IN ('1', '2') OR

                      (T1.REASON = '0' AND T1.ONLINEFLAG = '1'))

                GROUP BY T1.BOOKACCOUNT) B

        WHERE A.BOOKACCOUNT = B.BOOKACCOUNT(+)

          AND S.BOOKACCOUNT = A.BOOKACCOUNT

          AND S.BOOKACCOUNT LIKE '13500000%') T

ON (T.ROWIDS = S.ROWID)

WHEN MATCHED THEN

  UPDATE

     SET S.LASTBALANCE = T.BANKAMT,

         S.CURRBALANCE = T.BANKAMT,

         S.DEBITAMT    = 0.00,

         S.CREDITAMT   = 0.00

Plan Hash Value  : 273017430

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

| Id   | Operation                           | Name              | Rows   | Bytes    | Cost   | Time     |

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

|    0 | MERGE STATEMENT                     |                   | 152885 |  4280780 | 283362 | 00:56:41 |

|    1 |   MERGE                             | TLHRBOKBAL        |        |          |        |          |

|    2 |    VIEW                             |                   |        |          |        |          |

|    3 |     NESTED LOOPS                    |                   | 152885 | 20945245 | 283362 | 00:56:41 |

|  * 4 |      HASH JOIN RIGHT OUTER          |                   | 152885 | 14065420 | 130342 | 00:26:05 |

|    5 |       VIEW                          |                   |    124 |     3844 |  15668 | 00:03:09 |

|    6 |        SORT GROUP BY                |                   |    124 |     5084 |  15668 | 00:03:09 |

|  * 7 |         TABLE ACCESS BY INDEX ROWID | TLHRBOKBALJN      |    124 |     5084 |  15668 | 00:03:09 |

|  * 8 |          INDEX RANGE SCAN           | PK_TLHRBOKBALJN   |    165 |          |  15501 | 00:03:07 |

|  * 9 |       HASH JOIN                     |                   | 152885 |  9325985 | 114671 | 00:22:57 |

|   10 |        TABLE ACCESS BY INDEX ROWID  | TLHRBOKBAL_TMP    | 153563 |  4146201 | 112930 | 00:22:36 |

| * 11 |         INDEX RANGE SCAN            | PK_TLHRBOKBAL_TMP | 153563 |          |   1159 | 00:00:14 |

| * 12 |        INDEX RANGE SCAN             | PK_TLHRBOKBAL     | 152884 |  5198056 |   1117 | 00:00:14 |

|   13 |      TABLE ACCESS BY USER ROWID     | TLHRBOKBAL        |      1 |       45 |      1 | 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

* 4 - access("NB"."BOOKACCOUNT"="B"."BOOKACCOUNT"(+))

* 7 - filter("T1"."REASON"='0' AND "T1"."ONLINEFLAG"='1' OR "T1"."REASON"='1' OR "T1"."REASON"='2')

* 8 - access("T1"."BOOKACCOUNT" LIKE '13500000%' AND "T1"."TRANDATE"='20150901')

* 8 - filter("T1"."BOOKACCOUNT" LIKE '13500000%' AND "T1"."TRANDATE"='20150901')

* 9 - access("S"."BOOKACCOUNT"="NB"."BOOKACCOUNT")

* 11 - access("NB"."BOOKACCOUNT" LIKE '13500000%')

* 11 - filter("NB"."BOOKACCOUNT" LIKE '13500000%')

* 12 - access("S"."BOOKACCOUNT" LIKE '13500000%')

* 12 - filter("S"."BOOKACCOUNT" LIKE '13500000%')

 

执行计划中,基本都走了索引了,跑了一下,大约1分种多,但是里边有个HINTS,分析了一下表TLHRBOKBAL_TMP上的索引情况,发现是个主键索引,且有2个列(BOOKACCOUNT,CURRENCY),但是不包含列CURRBALANCE,可能是Oracle觉得回表读的耗费比较大吧,那这里可以使用虚拟索引测试一下索引的性能:

CREATE INDEX IX_VI01_ID ON TLHR.TLHRBOKBAL_TMP(CURRBALANCE, CURRENCY,BOOKACCOUNT) NOSEGMENT;

ALTER SESSION SET "_USE_NOSEGMENT_INDEXES"=TRUE;

EXPLAIN PLAN FOR  MERGE INTO TLHR.TLHRBOKBAL S

USING (SELECT S.ROWID ROWIDS,

              A.BOOKACCOUNT AS BOOKACCOUNT,

              (A.CURRBALANCE + NVL(B.BAL, 0.00)) AS BANKAMT

         FROM (SELECT NB.CURRBALANCE,NB.BOOKACCOUNT

                 FROM TLHR.TLHRBOKBAL_TMP NB

                WHERE NB.BOOKACCOUNT LIKE '13500000%') A,

              TLHR.TLHRBOKBAL S,

              (SELECT T1.BOOKACCOUNT AS BOOKACCOUNT,

                      SUM(DECODE(T1.DCFLAG, 'D', -T1.AMT, 'C', T1.AMT, 0)) AS BAL

                 FROM TLHR.TLHRBOKBALJN T1

                WHERE T1.BOOKACCOUNT LIKE '13500000%'

                  AND T1.TRANDATE = '20150901'

                  AND (T1.REASON IN ('1', '2') OR

                      (T1.REASON = '0' AND T1.ONLINEFLAG = '1'))

                GROUP BY T1.BOOKACCOUNT) B

        WHERE A.BOOKACCOUNT = B.BOOKACCOUNT(+)

          AND S.BOOKACCOUNT = A.BOOKACCOUNT

          AND S.BOOKACCOUNT LIKE '13500000%') T

ON (T.ROWIDS = S.ROWID)

WHEN MATCHED THEN

  UPDATE

     SET S.LASTBALANCE = T.BANKAMT,

         S.CURRBALANCE = T.BANKAMT,

         S.DEBITAMT    = 0.00,

         S.CREDITAMT   = 0.00;

SELECT * FROM TABLE(DBMS_XPLAN.display);

Plan hash value: 983878991

 

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

| Id  | Operation                         | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

|   0 | MERGE STATEMENT                   |                 |   152K|  4180K|       |   170K  (1)| 00:34:06 |

|   1 |  MERGE                            | TLHRBOKBAL      |       |       |       |            |          |

|   2 |   VIEW                            |                 |       |       |       |            |          |

|   3 |    NESTED LOOPS                   |                 |   152K|    19M|       |   170K  (1)| 00:34:06 |

|*  4 |     HASH JOIN RIGHT OUTER         |                 |   152K|    13M|       | 17421   (1)| 00:03:30 |

|   5 |      VIEW                         |                 |   124 |  3844 |       | 15668   (1)| 00:03:09 |

|   6 |       SORT GROUP BY               |                 |   124 |  5084 |       | 15668   (1)| 00:03:09 |

|*  7 |        TABLE ACCESS BY INDEX ROWID| TLHRBOKBALJN    |   124 |  5084 |       | 15668   (1)| 00:03:09 |

|*  8 |         INDEX RANGE SCAN          | PK_TLHRBOKBALJN |   165 |       |       | 15501   (1)| 00:03:07 |

|*  9 |      HASH JOIN                    |                 |   152K|  9107K|  5856K|  1750   (1)| 00:00:22 |

|* 10 |       INDEX FAST FULL SCAN        | IX_VI01_ID      |   153K|  4049K|       |     9   (0)| 00:00:01 |

|* 11 |       INDEX RANGE SCAN            | PK_TLHRBOKBAL   |   152K|  5076K|       |  1117   (1)| 00:00:14 |

|  12 |     TABLE ACCESS BY USER ROWID    | TLHRBOKBAL      |     1 |    45 |       |     1   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   4 - access("NB"."BOOKACCOUNT"="B"."BOOKACCOUNT"(+))

   7 - filter("T1"."REASON"='0' AND "T1"."ONLINEFLAG"='1' OR "T1"."REASON"='1' OR "T1"."REASON"='2')

   8 - access("T1"."BOOKACCOUNT" LIKE '13500000%' AND "T1"."TRANDATE"='20150901')

       filter("T1"."BOOKACCOUNT" LIKE '13500000%' AND "T1"."TRANDATE"='20150901')

   9 - access("S"."BOOKACCOUNT"="NB"."BOOKACCOUNT")

  10 - filter("NB"."BOOKACCOUNT" LIKE '13500000%')

  11 - access("S"."BOOKACCOUNT" LIKE '13500000%')

       filter("S"."BOOKACCOUNT" LIKE '13500000%')

 

说明创建3个列的索引是可以的。我们先将该虚拟索引删除DROP INDEX IX_VI01_ID;

3.2.1  ITL死锁问题解决

另外一个问题,是开发说上边的SQL语句产生了死锁,起初我还半信半疑,先去告警日志中用命令(more alert* | grep Deadlock)搜了一下:

wpsA841.tmp 

结果发现很多的死锁,拿到相关的文件,看到如下一段:

user session for deadlock lock 0x7000008094d14e0

  sid: 332 ser: 47221 audsid: 991000 user: 84/TLHR

    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

    flags2: (0x40009) -/-/INC

  pid: 101 O/S info: user: grid, term: UNKNOWN, ospid: 6489034

    image: oracle@ZFTLHRDB1

  client details:

    O/S info: user: TLHR, term: , ospid: 34406578

    machine: ZFTLHRAP1 program: bat_CheckBookBal@ZFTLHRAP1 (TNS V1-V3)

    application name: bat_CheckBookBal@ZFTLHRAP1 (TNS V1-V3), hash value=446537749

  current SQL:

   MERGE INTO TLHRBOKBAL S USING  (SELECT A.BOOKACCOUNT AS BOOKACCOUNT,  (A.CURRBALANCE + nvl(B.BAL, 0.00)) AS BANKAMT  FROM  TLHRBOKBAL_TMP A, (SELECT T1.BOOKACCOUNT AS BOOKACCOUNT,  SUM(DECODE(T1.DCFLAG, 'D', -T1.AMT, 'C', T1.AMT, 0)) AS BAL  FROM TLHRBOKBALJN T1  WHERE T1.BOOKACCOUNT LIKE '13450000'||'%'  AND T1.TRANDATE='20160901'  AND (T1.REASON = '2' OR T1.REASON = '1' OR  (T1.REASON = '0' AND T1.ONLINEFLAG = '1'))  GROUP BY T1.BOOKACCOUNT) B  WHERE A.BOOKACCOUNT = B.BOOKACCOUNT(+)  AND A.BOOKACCOUNT LIKE '13450000'||'%') T  ON (S.BOOKACCOUNT = T.BOOKACCOUNT)  WHEN MATCHED THEN UPDATE  SET S.LASTBALANCE=T.BANKAMT,S.CURRBALANCE=T.BANKAMT,S.DEBITAMT=0.00,S.CREDITAMT=0.00

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
【技巧】如何使用客户端发布 BLOG+ 如何快速发布微信公众号文章   1    BLOG 文档结构图     2    前言部分   2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ①  如何使用客户端发布 BLOG (重点) ②  如何快速发布微信公众号文章 ③  如何批量上传图片到blog ④  如何批量上传图片到微信公众号上     Tips: ① 本文在 itpub ( http://blog.itpu

降低高水位线的方法 - 2016-09-13 14:09:09

准备工作,创建一张表: CREATE TABLE TEST2 (     ID    NUMBER(10),     NAME  VARCHAR2(32) ); 1、移动表: SQL begin   2  for i in 1..10000 loop   3  insert into test2 values(i,'bbb');   4  end loop;   5  commit;   6  end;   7  / PL/SQL procedure successfully completed. SQL
预备知识: 1)row cache lock事件   --内存share pool分为library cache,dictionary cache;而row cache lock对象分布在dictionary cache中,是由于对于字典缓冲的访问造成的;   --这类属于latch类的资源竞争,相当耗CPU,如果并发量大的话,容易down机 如果发现这个等待十分高,一般来说可能由于2种原因,一是共享池太小了,需要增加共享池,另外一种情况是SQL分析过于频繁,对于共享池的并发访问量过大。对于任何一种情况,绝
【故障处理】队列等待之 TX - allocate ITL entry 案例 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识, ~O(∩_∩)O~ : ① enq: TX - allocate ITL entry 等待事件的解决 ② 一般等待事件的解决办法 ③ 队列等待的基本知识 Tips: ① 本文在 ITpub ( http://blog.itpub.net/267361
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。当然,可以将现有的主键索引先删掉,但这样在新索引建立之前,无法保证数据的唯一性,并且按主键的更新删除操作会因为无索引而变的很慢。        我们可