联系：手机/微信(+86 17813235971) QQ(107644445)
标题：收集统计信息出现ORA-00600[ksxprqfre3]
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
alert日志出现ORA-00600[ksxprqfre3]错误
Sun Mar 18 22:11:44 2012 Errors in file /oracle9/app/admin/offon/bdump/offon2_p001_8220790.trc: ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033D5A8], [], [], [], [], [], [] Sun Mar 18 22:11:44 2012 Errors in file /oracle9/app/admin/offon/bdump/offon2_p001_8220790.trc: ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033E178], [], [], [], [], [], []
通过这里可以发现，是由于并发进程导致异常
trace文件内容
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name: AIX
Node name: zwq_offon2
Release: 3
Version: 5
Machine: 00CA56A44C00
Instance name: offon2
Redo thread mounted by this instance: 2
Oracle process number: 328
Unix process pid: 8220790, image: oracle@zwq_offon2 (P001)
*** 2012-03-18 22:11:43.994
*** SESSION ID:(381.10338) 2012-03-18 22:11:43.989
krqh = 11033d5a8
rqh trace:
Slot: 6 Op: 114 ksxpirqh: irqh requested
Slot: 7 Op: 130 ksxpirqh: rqh SUCC
Slot: 8 Op: 123 ksxpwait: receive mv to doneq
Slot: 9 Op: 121 ksxprcv: rqh found on doneq
Slot: 10 Op: 102 ksxprqfre: freed to queue
Slot: 11 Op: 101 ksxprqalo: allocated
Slot: 12 Op: 105 ksxpdteq: enqueue to delta q
Slot: 13 Op: 117 ksxpvsnd: send via ksxpisnd
Slot: 14 Op: 106 ksxpisnd: send requested
Slot: 0 Op: 107 ksxpisnd: send submit OK
Slot: 1 Op: 153 pre send completion cbk
Slot: 2 Op: 102 ksxprqfre: freed to queue
Slot: 3 Op: 114 ksxpirqh: irqh requested
Slot: 4 Op: 130 ksxpirqh: rqh SUCC
Slot: 5 Op: 153 pre send completion cbk
Dump of memory from 0x000000011033D5A8 to 0x000000011033D660
11033D5A0 00000001 1033D5A8 [.....3..]
11033D5B0 00000001 1033D5A8 00000001 1033D5B8 [.....3.......3..]
11033D5C0 00000001 1033D5B8 00000006 00000001 [.....3..........]
11033D5D0 00000000 00000000 00000000 00000000 [................]
11033D5E0 00000572 00000000 07000000 EE473B28 [...r.........G;(]
11033D5F0 00010000 00000000 00000001 1029CEA8 [.............)..]
11033D600 00000001 104382A0 07000000 F279B5A8 [.....C.......y..]
11033D610 00000000 00000572 00000000 00000000 [.......r........]
11033D620 00000000 00000000 00000000 13DCD225 [...............%]
11033D630 13DA7A25 00010001 26750000 00000000 [..z%....&u......]
11033D640 00000001 10339C70 6B996672 82997282 [.....3.pk.fr..r.]
11033D650 7B796665 69756A06 00000001 1033E228 [{yfeiuj......3.(]
*** 2012-03-18 22:11:43.996
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033D5A8], [], [], [], [], [], []
Current SQL statement for this session:
SELECT /*+ PIV_SSF */ SYS_OP_MSR(SYS_OP_COUNTCHG(SUBSTRB(ROWIDTOCHAR(A1.C0),1,15),1),COUNT(DISTINCT A1.C3),COUNT(*)) C0 FROM (SELECT /*+ INDEX_RRS(A2 "PK_TINEXWORK_DATA
") */ A2.ROWID C0,A2."WORKSHTSN" C1,A2."EXWORKSN" C2,SYS_OP_LBID(8574,'L',A2.ROWID) C3 FROM "OFFON"."TINEXWORK_DATA" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A2) A1
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148 bl ksedst 102973B94 ?
ksfdmp+0018 bl 01FD34D8
kgerinv+00e8 bl _ptrgl
kgeasnmierr+004c bl kgerinv 127C7F9D7226029B ?
9770A0AFAF4BDC0 ?
12E0BE826D694B2F ?
000000000 ? 000000010 ?
ksxprqfre+0054 bl kgeasnmierr 110006728 ? 110338C88 ?
1030069D4 ? 100000001 ?
000000002 ? 11033D5A8 ?
080000000 ?
7F7F7F7F7F7F7F7F ?
ksxpwait+06bc bl ksxprqfre 7000000D6D793D8 ?
ksliwat+02e0 bl ksxpwait 1014F4508 ? 1101FB1C8 ?
kslwaitns+001c bl ksliwat FFFFFFFFFFFC4B0 ? 000000004 ?
FFFFFFFFFFFC590 ? 11030BC60 ?
FFFFFFFFFFFC530 ?
7000000DDF1FBE0 ? 110006868 ?
kskthbwt+01b4 bl kslwaitns 000000002 ? 7000000EAEEB7A0 ?
000000000 ? 000000000 ?
000000868 ? 7000000DAFEFB30 ?
7000000F4979B10 ?
kslwait+005c bl kskthbwt FFFFFFFFFFFC6E0 ?
2422424800000000 ?
1015EDD34 ? 110305194 ?
FFFF0000000003 ? 000000000 ?
000000001 ? FFFFFFFFFFFF920 ?
ksxprcv+0184 bl kslwait 102970928 ? 000000006 ?
000000000 ? 000000000 ?
110305148 ? 110006728 ?
kxfpqidqr+0600 bl ksxprcv 11029CEA8 ? 000000008 ?
FFFFFFFFFFFD020 ?
FFFFFFFFFFFCFC0 ?
162DFEF4180 ? 000000000 ?
000000000 ? 000000000 ?
kxfpqdqr+02c0 bl kxfpqidqr 11029CF80 ? 11029CF20 ?
FFFFFFFFFFFD7C0 ?
7000000EAE2DA30 ?
kxfxgs+0050 bl kxfpqdqr 101518AF0 ? 000002000 ?
FFFFFFFFFFFD9F0 ? 000000000 ?
kxfxmai+0100 bl kxfxgs 102970928 ? 000000060 ?
7000000EEF059F0 ?
70000000006D670 ?
kxfprdp+05b0 bl _ptrgl
opirip+0390 bl kxfprdp
opidrv+0300 bl opirip 000000018 ? 0101FB1C8 ?
000000000 ?
sou2o+0028 bl opidrv 320C000000 ? 0A00E8B50 ?
000000000 ?
main+01a4 bl 01FD2EF0
__start+0098 bl main 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
SO: 7000000cf267198, type: 4, owner: 7000000cd2a53e0, flag: INIT/-/-/0x00
(session) trans: 0, creator: 7000000cd2a53e0, flag: (48000041) USR/- -/-/-/-/-/-
DID: 0002-0148-003F8809, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 0, prv: 0, sql: 0, psql: 0, user: 25/MONITOR
O/S info: user: maint1, term: , ospid: 8220790, machine: zwq_acc2
program: oracle@zwq_offon2 (P001)
application name: SQL*Plus, hash value=3669949024
waiting for 'PX Deq: reap credit' blocking sess=0x0 seq=182 wait_time=0
=0, =0, =0
temporary object counter: 0
这里可以得出信息如下：
1)系统环境AIX 5.3 9.2.0.8.0 RAC
2)报错的语句是收集TINEXWORK_DATA表相关统计信息
3)是由于SQL*Plus收集统计信息使用并发导致该错误
查询MOS发现
Bug 5887047: ORA-00600 [KSXPRQFRE3] TRACE WHEN GATHERING OPTIMIZER STATISTICS描述相符：
1）数据库版本9.2.0.8
2）收集统计信息操作导致
3）STACK TRACE一致
处理建议
针对官方没有给出明确的解决方案，因为该问题出现概率比较低，如果经常出现需要考虑升级数据库版本