OGG-DEADLOCK

Trace文件中的ASH显示等待情况 可以发现集中在ITL上面

0: waited for 'enq: TX - allocate ITL entry'

       name|mode=0x54580004, usn<<16 | slot=0xab000f, sequence=0x71b8

       wait_id=14471029 seq_num=53335 snap_id=1

       wait times: snap=5.001907 sec, exc=5.001907 sec, total=5.001907 sec

       wait times: max=5.000000 sec

       wait counts: calls=2 os=2

       occurred after 0.000074 sec of elapsed time

    1: waited for 'enq: TX - allocate ITL entry'

       name|mode=0x54580004, usn<<16 | slot=0x65001f, sequence=0xfe6f

       wait_id=14471028 seq_num=53334 snap_id=1

       wait times: snap=4.001991 sec, exc=4.001991 sec, total=4.001991 sec

       wait times: max=4.000000 sec

       wait counts: calls=2 os=2

       occurred after 0.000039 sec of elapsed time

    2: waited for 'enq: TX - allocate ITL entry'

       name|mode=0x54580004, usn<<16 | slot=0xab000f, sequence=0x71b8

       wait_id=14471027 seq_num=53333 snap_id=1

       wait times: snap=4.001925 sec, exc=4.001925 sec, total=4.001925 sec

       wait times: max=4.000000 sec

       wait counts: calls=2 os=2

       occurred after 0.000061 sec of elapsed time

    3: waited for 'enq: TX - allocate ITL entry'

       name|mode=0x54580004, usn<<16 | slot=0x65001f, sequence=0xfe6f

       wait_id=14471026 seq_num=53332 snap_id=1

       wait times: snap=2.000961 sec, exc=2.000961 sec, total=2.000961 sec

       wait times: max=2.000000 sec

       wait counts: calls=1 os=1

       occurred after 0.000059 sec of elapsed time

    4: waited for 'enq: TX - allocate ITL entry'

       name|mode=0x54580004, usn<<16 | slot=0xab000f, sequence=0x71b8

       wait_id=14471025 seq_num=53331 snap_id=1

       wait times: snap=2.000916 sec, exc=2.000916 sec, total=2.000916 sec

       wait times: max=2.000000 sec

       wait counts: calls=1 os=1

       occurred after 0.000084 sec of elapsed time

    5: waited for 'enq: TX - allocate ITL entry'

       name|mode=0x54580004, usn<<16 | slot=0x65001f, sequence=0xfe6f

       wait_id=14471024 seq_num=53330 snap_id=1

       wait times: snap=1.000939 sec, exc=1.000939 sec, total=1.000939 sec

       wait times: max=1.000000 sec

       wait counts: calls=1 os=1

       occurred after 0.000043 sec of elapsed time

    6: waited for 'enq: TX - allocate ITL entry'

       name|mode=0x54580004, usn<<16 | slot=0xab000f, sequence=0x71b8

       wait_id=14471023 seq_num=53329 snap_id=1

       wait times: snap=1.000679 sec, exc=1.000679 sec, total=1.000679 sec

       wait times: max=1.000000 sec

       wait counts: calls=1 os=1

       occurred after 0.000131 sec of elapsed time

    7: waited for 'SQL*Net message from client'

       driver id=0x62657100, #bytes=0x1, =0x0

       wait_id=14471022 seq_num=53328 snap_id=1

       wait times: snap=0.000212 sec, exc=0.000212 sec, total=0.000212 sec

       wait times: max=infinite

       wait counts: calls=0 os=0

       occurred after 0.000005 sec of elapsed time

    8: waited for 'SQL*Net message to client'

       driver id=0x62657100, #bytes=0x1, =0x0

       wait_id=14471021 seq_num=53327 snap_id=1

       wait times: snap=0.000002 sec, exc=0.000002 sec, total=0.000002 sec

       wait times: max=infinite

       wait counts: calls=0 os=0

       occurred after 0.000167 sec of elapsed time

    9: waited for 'db file sequential read'

       file#=0x7, block#=0x48942, blocks=0x1

       wait_id=14471020 seq_num=53326 snap_id=1

       wait times: snap=0.009281 sec, exc=0.009281 sec, total=0.009281 sec

       wait times: max=infinite

       wait counts: calls=0 os=0

       occurred after 0.000024 sec of elapsed time

 

 

处理办法：

1、停止该对象所有相关复制进程

2、ALTER TABLEBFMOBILE_SYNC.ACCT_PAYMENT_SCHEDULE move  INITRANS复制队列数量

3、索引重建参考

alter index index_name rebuild parallel degree;--根据系统资源开启并行

alter index index_name noparallel;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31134212/viewspace-2127483/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31134212/viewspace-2127483/