0

ORA-600: [kkoipt:invalid join method]

老杨 2019-04-18
139
摘要:客户的11.2.0.2数据库碰到了这个错误。详细错误信息如下:FriSep1615:23:522011ErrorsINfile/u01/d...

问题描述

客户的11.2.0.2数据库碰到了这个错误。
详细错误信息如下:

Fri Sep 16 15:23:52 2011
Errors IN file /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc (incident=169704):
ORA-00600: 内部错误代码, 参数: [kkoipt:invalid JOIN method], [1], [0], [], [], [], [], [], [], [], [], []
Incident details IN: /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc
Fri Sep 16 15:24:00 2011
Dumping diagnostic DATA IN directory=[cdmp_20110916152400], requested BY (instance=1, osid=20382140), summary=[incident=169704].
USE ADRCI OR Support Workbench TO package the incident.
See Note 411.1 at My Oracle Support FOR error AND packaging details.
而对应的详细TRACE如下:
bash-3.2$ more /u01/diag/rdbms/ora1/ora1/incident/incdir_169704/ora1_ora_20382140_i169704.trc
Dump file /u01/diag/rdbms/ora1/ora11/incident/incdir_169704/ora1_ora_20382140_i169704.trc
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
WITH the Partitioning, REAL Application Clusters, Automatic Storage Management, OLAP,
DATA Mining AND REAL Application Testing options
ORACLE_HOME = /u01/product/11.2.0/dbhome_1
System name:    AIX
Node name:      node1
Release:        1
Version:        6
Machine:        00F6CD264C00
Instance name: ora1
Redo thread mounted BY this instance: 1
Oracle process NUMBER: 193
Unix process pid: 20382140, image: oracle@s180
*** 2011-09-16 15:23:52.275
*** SESSION ID:(29.7169) 2011-09-16 15:23:52.275
*** CLIENT ID:() 2011-09-16 15:23:52.275
*** SERVICE NAME:(ora1) 2011-09-16 15:23:52.275
*** MODULE NAME:(TOAD 10.5.0.41) 2011-09-16 15:23:52.275
*** ACTION NAME:() 2011-09-16 15:23:52.275
Dump continued FROM file: /u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc
ORA-00600: 内部错误代码, 参数: [kkoipt:invalid JOIN method], [1], [0], [], [], [], [], [], [], [], [], []
========= Dump FOR incident 169704 (ORA 600 [kkoipt:invalid JOIN method]) ========
*** 2011-09-16 15:23:52.336
dbkedDefDump(): Starting incident DEFAULT dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7ukzmn3p6zby6) -----
SELECT *
  FROM (
  SELECT   a.childpolicyno, a.policyno, e.exportno corpno, a.effectdate,
                 TO_CHAR (a.lapsedate, 'yyyy-mm-dd') lapsedate, e.chnname,
                 e.address, e.keyflag, e.specialflag, g.empname,
                 g.secdeptname, f.TYPE, ROWNUM AS ID
            FROM t_schildpolicy a,
                 t_spolicy f,
                 t_exportcorp e,
                 t_nodeinfo d,
                 v_employeecustomer g
           WHERE f.policyno = a.policyno
             AND f.insurantno = e.exportno
             AND f.policyno = g.productid(+)
             AND d.nodeid = f.nodeid
             AND e.chnname LIKE '%%'
             AND ((d.corpid = '3502'))
             AND ROWNUM <= 10
        ORDER BY a.policyno)
 WHERE ID BETWEEN 1 AND 10
----- Call Stack Trace -----
calling              CALL     entry                argument VALUES IN hex      
location             TYPE     point                (? means dubious VALUE)     
-------------------- -------- -------------------- ----------------------------
skdstdst()+40        bl       107b6e01c            FFFFFFFFFFECCA8 ? 000002004 ?
                                                   000000001 ? 000000003 ?
                                                   000000000 ? 000000002 ?
                                                   000000001 ? 000000000 ?
ksedst1()+104        CALL     skdstdst()           FFFFFFFFFFEBCB0 ? 000002004 ?
                                                   110A597A0 ? 10A027B2C ?
                                                   110A597A0 ? 000000000 ?
                                                   FFFFFFFFFFEBDE0 ? 700000007 ?
ksedst()+40          CALL     ksedst1()            3030000000000 ? 002050033 ?
                                                   10A027B20 ? 700000000025C ?
                                                   000000000 ? 000000000 ?
                                                   10A027180 ? 000000000 ?
dbkedDefDump()+2828  CALL     ksedst()             FFFFFFFFFFEBE90 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 300000003 ?
ksedmp()+76          CALL     dbkedDefDump()       310A597A0 ? 1100010C8 ?
                                                   FFFFFFFFFFEC490 ?
                                                   28444040FFFEC66C ?
                                                   100148568 ? 1096635A8 ?
                                                   FFFFFFFFFFEC4E0 ? 11064B598 ?
ksfdmp()+88          CALL     ksedmp()             000000000 ? 000000000 ?
                                                   0096635C3 ? 109CB2C50 ?
                                                   200000000000000 ? 000000000 ?
                                                   110C221E8 ? 110A597A0 ?
dbgexPhaseII()+1212  CALL     ksfdmp()             000002004 ? 110A597A0 ?
                                                   000000000 ? FFFFFFFFFFEC658 ?
                                                   FFFFFFFFFFEC580 ?
                                                   FFFFFFFFFFECCA8 ? 1001D04B8 ?
                                                   110C221E8 ?
dbgexProcessError()  CALL     dbgexPhaseII()       110A597A0 ? 110C203F8 ?
+3604                                              0000296E8 ? 200000000 ?
                                                   FFFFFFFFFFED258 ? 00000006A ?
                                                   000000000 ? 000000000 ?
dbgeExecuteForError  CALL     dbgexProcessError()  110A597A0 ? 110C221E8 ?
()+72                                              100000000 ? 000000000 ?
                                                   110D01C88 ? 000000000 ?
                                                   110CADC78 ? 110C23F30 ?
dbgePostErrorKGE()+  CALL     dbgeExecuteForError  FFFFFFFFFFF0830 ?
1152                          ()                   B7417335409B9B1B ?
                                                   FFFFFFFFFFF06F0 ? 0409B9800 ?
                                                   10524EA10 ?
                                                   2147AE154168E65F ?
                                                   10524EA10 ? 000000000 ?
dbkePostKGE_kgsf()+  CALL     dbgePostErrorKGE()   002050000 ? 001160000 ?
64                                                 25810001330 ? 000000000 ?
                                                   110001330 ? FFFFFFFFFFF1510 ?
                                                   109613CD0 ? 110CB3F18 ?


专家解答

这个SQL错误的奇特之处在于,如果将ORDER BY语句中的a.policyno变成其他列,则不会导致错误,甚至是换成与之相等关联的f.policyno,同样不会报错。
这显然是一个bug,查询metalink发现,居然目前唯一确认影响的版本就是11.2.0.2,这个Bug的描述为Bug 12591252 Query with ORDER BY fails with ORA-600 [kkoipt:invalid join method]。而Oracle的解释是,优化器试图通过索引来避免排序操作时,可能引发这个bug。这正好说明了为什么SQL中换成其他的列就不会导致错误。
目前这个bug在Windows平台的11.2.0.2的patch 10中被fixed,其他平台只能通过单独的12591252补丁来解决这个问题。当然如果能通过改写SQL来实现这个目的,无疑代价是最小的。

「喜欢文章,快来给作者赞赏墨值吧」

评论

0
0
Oracle
订阅
欢迎订阅Oracle频道,订阅之后可以获取最新资讯和更新通知。
墨值排行
今日本周综合
近期活动
全部
相关课程
全部