Oracle: ORA-600 error [kkoept: invalid join method]

Today the customer's Oracle 11.2.0.2 database encountered the ORA-600 error.

The error details are as follows:

Fri Sep 16 15:23:52 2011
Errors in file/u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc (incident = 169704 ):
ORA-00600: Internal error code, parameter: [kkoept: 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_20151116152400], 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.

The detailed TRACE is as follows:

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: 00f6cd0000c00
Instance name: ora1
Redo thread mounted by this instance: 1
Oracle process number: 193
Unix process pid: 20382140, image: oracle @ fig

* ** 15:23:52. 275
* ** Session id: (29.7169) 15:23:52. 275
* ** Client id: () 15:23:52. 275
* ** Service name :( ora1) 15:23:52. 275
* ** Module name :( TOAD 10.5.0.41) 15:23:52. 275
* ** Action name :() 15:23:52. 275

Dump continued from file:/u01/diag/rdbms/ora1/ora1/trace/ora1_ora_20382140.trc
ORA-00600: Internal error code, parameter: [kkoept: invalid join method], [1], [0], [], [], [], [], [], [], [], [], []

========= Dump for incident 169704 (ORA 600 [kkoept: invalid join method]) ========

* ** 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. polictdate,
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,
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 = '123 '))
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 rjb6e01c FFFFFFFFFFECCA8? 000002004?
000000001? 000000003?
000000000? 000000002?
000000001? 000000000?
Ksedst1 () + 104 call skdstdst () ffffffffebcb0? 000002004?
110A597A0? 10A027B2C?
110A597A0? 000000000?
Ffffffffebde0? 700000007?
Ksedst () + 40 call ksedst1 () 3030000000000? 002050033?
10A027B20? 700000000025C?
000000000? 000000000?
10A027180? 000000000?
DbkedDefDump () + 2828 call ksedst () ffffffffebe90? 000000000?
000000000? 000000000?
000000000? 000000000?
000000000? 300000003?
Ksedmp () + 76 call dbkedDefDump () 310A597A0? 1100010C8?
Ffffffffec490?
28444040FFFEC66C?
100148568? Why 6635a8?
Ffffffffec4e0? 11064B598?
Ksfdmp () + 88 call ksedmp () 000000000? 000000000?
0096635C3? Why cb2c50?
200000000000000? 000000000?
110C221E8? 110A597A0?
DbgexPhaseII () + 1212 call ksfdmp () 000002004? 110A597A0?
000000000? FFFFFFFFFFEC658?
FFFFFFFFFFEC580?
Ffffffffecca8? 1001D04B8?
110C221E8?
DbgexProcessError () call dbgexPhaseII () 110A597A0? 110C203F8?
+ 3604 listen 296e8? 200000000?
Ffffffffed258? 00000006A?
000000000? 000000000?
DbgeExecuteForError call dbgexProcessError () 110A597A0? 110C221E8?
() Plus 72 100000000? 000000000?
110D01C88? 000000000?
110CADC78? 110C23F30?
DbgePostErrorKGE () + call dbgeExecuteForError FFFFFFFFFFF0830?
1152 () b70000335409b9b1b?
FFFFFFFFFFF06F0? 0409B9800?
10524EA10?
2147ae15da-e65f?
10524EA10? 000000000?
DbkePostKGE_kgsf () + call dbgePostErrorKGE () 002050000? 001160000?
64 25810001330? 000000000?
110001330? Fffffffff1510?
109613CD0? 110CB3F18?

The uniqueness of this SQL error lies in the fact that. changing policyno to another column will not cause errors, or even replace it with the f. policyno, and no error will be reported.

This is obviously a bug, the Query metalink found that the only currently confirmed version of the impact is 11.2.0.2, the Bug is described as Bug 12591252 Query with order by fails with ORA-600 [kkoept: invalid join method]. Oracle explains that this bug may occur when the optimizer tries to avoid sorting operations through indexes. This explains why changing to another column in SQL does not cause errors.

Currently, this bug is fixed in patch 10 of 11.2.0.2 on Windows. Other platforms can only solve this problem through a separate patch 12591252. Of course, if you can rewrite SQL to achieve this goal, the cost is undoubtedly the smallest.