联系:手机/微信(+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一致
处理建议
针对官方没有给出明确的解决方案,因为该问题出现概率比较低,如果经常出现需要考虑升级数据库版本
Bug 5887047: ORA-00600 [KSXPRQFRE3] TRACE WHEN GATHERING OPTIMIZER STATISTICS