收集统计信息出现ORA-00600[ksxprqfre3]

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:收集统计信息出现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一致

处理建议
针对官方没有给出明确的解决方案,因为该问题出现概率比较低,如果经常出现需要考虑升级数据库版本

此条目发表在 ORA-xxxxx 分类目录,贴了 标签。将固定链接加入收藏夹。

收集统计信息出现ORA-00600[ksxprqfre3]》有 1 条评论

  1. 惜分飞 说:

    Bug 5887047: ORA-00600 [KSXPRQFRE3] TRACE WHEN GATHERING OPTIMIZER STATISTICS

    Hdr: 5887047 9.2.0.8 RDBMS 9.2.0.8 DICTIONARY PRODID-5 PORTID-212
    Abstract: ORA-600 [KSXPRQFRE3] TRACE  WHEN GATHERING OPTIMIZER STATISTICS
    
    *** 02/16/07 01:32 am ***
    TAR:
    ----
    
    PROBLEM:
    --------
    Following error occurs when collecting statistics.
    
    execute package Pkg_Stats.GatherDatabase
    
     
    ORA-12805: parallel query server died unexpectedly
    ORA-600: internal error code, arguments: [ksxprqfre3], [0x11036E430],     
    ORA-10382: parallel query server interrupt (reset)
    
    
    
    DIAGNOSTIC ANALYSIS:
    --------------------
    
    The error does not match with any known bugs.
    
    
    Ksedmp: internal or fatal error
    ORA-600: internal error code, arguments: [ksxprqfre3], [0x11036E430], [],   
    ORA-10382: parallel query server interrupt (reset)
    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.C2),COUNT(*)) C0  FROM (SELECT /*+ INDEX_RRS(A2 "PK_LG_LAGERBEWEGUNG") */ 
    A2.ROWID C0,A2."BEWNR"  C1,SYS_OP_LBID(28270,'L',A2.ROWID) C2 FROM 
    "VS_USER"."LG_LAGERBEWEGUNG"  PX_GRANULE(0,
    BLOCK_RANGE, DYNAMIC) SAMPLE BLOCK(12.531818) A2) A1
    
    WORKAROUND:
    -----------
    
    RELATED BUGS:
    -------------
    
    REPRODUCIBILITY:
    ----------------
    The error is reprodue everytime when the customer collect the statistics.
    
    execute package Pkg_Stats.GatherDatabase
    
    TEST CASE:
    ----------
    
    STACK TRACE:
    ------------
    ksedmp ksfdmp kgerinv kgeasnmierr ksxprqfre ksliwat 
    kslwaitns kskthbwt ksxpsndsync kxfpiscmpl kxfpqsod_slv_rsg kxfpqsod 
    kxfprdp sou2o start