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/