0 积分	1 好友	1 主题

发消息

[常规总体性能调优] ORA-3136 ORA-00600 ORA-03113 高并发insert语句

1^#

发表于 2013-3-14 16:06:28 | 查看: 6111| 回复: 6

操作系统：AIX 5.3
数据库版本：10.2.0.4 rac
alert报错：
Thu Mar 14 10:54:51 2013
Trace dumping is performing id=[cdmp_20130314105451]
Thu Mar 14 10:54:53 2013
WARNING: inbound connection timed out (ORA-3136)
Thu Mar 14 10:54:54 2013
Errors in file /oracle/product/database/10.2.0.4/db_1/admin/dw/udump/dw2_ora_1269904.trc:
ORA-00600: internal error code, arguments: [kdtdelrow-2], [66], [66], [], [], [], [], []
ORA-03113: end-of-file on communication channel
Thu Mar 14 10:55:03 2013
WARNING: inbound connection timed out (ORA-3136)
Thu Mar 14 10:55:29 2013
Trace dumping is performing id=[cdmp_20130314105529]
Thu Mar 14 11:18:40 2013
Thread 2 advanced to log sequence 32671 (LGWR switch)
  Current log# 3 seq# 32671 mem# 0: /dev/rlv_2g_redo211
  Current log# 3 seq# 32671 mem# 1: /dev/rlv_2g_redo212

trace文件在附件中，想知道具体引起报错的原因，但是下面这个看不太懂,请大家指教：
A20B6EF1:6FD83AFE 226  1563 10401 1 KSXPVSND: client 2 tid(1,257,0x9d4e3ae5) buf 0x700000a0fe677d8 sz 128
A20B6F7B:6FD83B0E 226  1563 10425  31 kjusuc:[closeonerror]err 3113 on [0x15][0x471cb117],[FB] [lp 700000a0ac04980]
A20B6F81:6FD83B11 226  1563 10425  28 kjlrem:invlk[addr 700000a08906ba0][lp 700000a0ac04980][lstat 1][proc 700000a098b98e0][rp 700
000a0edae730][hist x5149a1b5]
A20B6F86:6FD83B13 226  1563 10425  23 resignal error 3113
A20B6F87:6FD83B15 226  1563 10704 166 ksqcmi: error ORA-3113 caught; location 5
A20B6F88:6FD83B16 226  1563 10704 167 ksqgelctx: error ORA-3113 caught; location 1
A20B6FA3:6FD83B1B 226  1563 10706 6 0x0000000000000037 0x0000000000000015 0x00000000471CB117 0x0000000040010001 0x00000000000000
05 0x0000000000000000
A20B6FA8:6FD83B1D 226  1563 10005 1 KSL WAIT BEG [enq: FB - contention] 1178730502/0x46420006 21/0x15 1193062679/0x471cb117
A20B7091:6FD83B29 226  1563 10401  66 KSXP_SND_CALLBACK:  request 0x0x110463308, status 30
A20B7093:6FD83B2A 226  1563 10401  71 KSXPWAIT: Send compl suppressed and No requests. proc 0x700000a084d5098 haswrk 0
A20B70DE:6FD83B3A 226  1563 10005 2 KSL WAIT END [enq: FB - contention] 1178730502/0x46420006 21/0x15 1193062679/0x471cb117 time
=311

dw2_ora_1269904.zip

490.19 KB, 下载次数: 1035

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2013-3-14 16:10:28

10.2.0.4 + RAC

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kdtdelrow-2], [66], [66], [], [], [], [], []
ORA-03113: end-of-file on communication channel
Current SQL statement for this session:
insert into IF_MS_MEDIA_TASK_REPORT_MW(MONTHDAY,MSGID,REPORTSTAT,SENDTIME,RECEIVERINFO) values (:MONTHDAY,:MSGID,:REPORTSTAT,:SENDTIME,:RECEIVERINFO)
----- Call Stack Trace -----
calling             call    entry             argument values in hex
location          type    point             (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c       bl    ksedst1             000000000 ? FFFFFFFFFFF67F4 ?
ksedmp+0290       bl    ksedst             104A2C690 ?
ksfdmp+0018       bl    03F26C3C
kgerinv+00dc       bl    _ptrgl
kgeasnmierr+004c    bl    kgerinv             000000000 ? 1050AC028 ?
                                                068220021 ? 008220021 ?
                                                1104D4160 ?
kdtDelRow+01a0    bl    01F94108
insdrv+0ad0       bl    kdtDelRow          1100DD648 ?
inscovexe+02b8    bl    insdrv             11049AE90 ?
insExecStmtExecIniE  bl    01F9410C
ngine+005c
insexe+02f8       bl    insExecStmtExecIniE  100000000000000 ? 000000000 ?
                           ngine             000000005 ?
opiexe+2738       bl    insexe             110498DF8 ? FFFFFFFFFFF8208 ?
kpoal8+0edc       bl    opiexe             1100DD648 ? 000000018 ?
                                                FFFFFFFFFFF9558 ?
opiodr+0ae0       bl    _ptrgl
ttcpip+1020       bl    _ptrgl
opitsk+1124       bl    01F96AC8

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2013-3-14 16:13:44

之前session在等 gc current request

SO: 700000a0f78a190, type: 4, owner: 700000a084d5098, flag: INIT/-/-/0x00
(session) sid: 1563 trans: 7000009e0d630c8, creator: 700000a084d5098, flag: (100041) USR/- BSY/-/-/-/-/-
            DID: 0002-00E2-0000037B, short-term DID: 0000-0000-00000000
            txn branch: 0
            oct: 2, prv: 0, sql: 700000985fdf948, psql: 700000985fdf948, user: 86/HEXINPINGTAI
service name: dw
O/S info: user: SYSTEM, term: WINDOWS-4QHZSTO, ospid: 25484:37116, machine: WORKGROUP\WINDOWS-4QHZSTO
            program: SendReportImporter.exe
application name: SendReportImporter.exe, hash value=3598495804
last wait for 'gc current request' blocking sess=0x0 seq=25679 wait_time=1914 seconds since wait started=0
            file#=1e4, block#=1c910, id#=2000001
Dumping Session Wait History
   for 'gc current block 2-way' count=1 wait_time=1914
            =1e4, =1c910, =2000001
   for 'gc current grant 2-way' count=1 wait_time=1780
            =11c, =1cb1f8, =2000001
   for 'gc current multi block request' count=1 wait_time=1326
            file#=11c, block#=1cb1f7, id#=2000001

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2013-3-14 16:22:10

The Bug 4668719 - ORA-600 [KDTDELROW-2] FROM BULK INSERT is fixed in 11, and as of the 10.2.0.4.0 patchset.

A workaround for this bug is not to use save exception mode of bulk insert.

There are some one off backports available for this bug. Check if there is a one off backport for your version/operating system, see Patch 4668719.

workaround ：

1. 不要用bulk insert

or

2. 减少单次bulk insert的行数

可能的 workaround:

1. 避免遇到gc current request 调优这个 event

回复只看该作者道具举报