在给客户巡检时，发现告警日志存在ORA-00600错误，客户的环境是Solaris 10，ORACLE 11.2.0.2.0，通过查看trace文件发现是ORACLE的SQL优化器在试图优化一条占用资源较大的SQL时触发的一个BUG（Bug 13616375），下面是相关trace信息：

*** 2012-09-07 22:23:06.382
*** SESSION ID:(4508.11065) 2012-09-07 22:23:06.382
*** CLIENT ID:() 2012-09-07 22:23:06.382
*** SERVICE NAME:(SYS$USERS) 2012-09-07 22:23:06.382
*** MODULE NAME:(DBMS_SCHEDULER) 2012-09-07 22:23:06.382
*** ACTION NAME:(ORA$AT_SQ_SQL_SW_1604) 2012-09-07 22:23:06.382
Dump continued from file: /opt/oracle/app/oracle/diag/rdbms/emsta/emsta1/trace/emsta1_j002_1
4294.trc
ORA-00600: internal error code, arguments: [qkaffsindex5], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 158201 (ORA 600 [qkaffsindex5]) ========
*** 2012-09-07 22:23:06.408
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=071nkg515nxkx) -----
/* SQL Analyze(4508,1) */select rpt_id,rpt_name,MAX(aa),MAX(bb),MAX(cc),MAX(dd),MAX(ee) from
(SELECT --RPT_ID, RPT_NAME, CLICK_AMOUNT, CLCT_DAY
       rpt_id,rpt_name,
       CASE WHEN rn = 1 THEN clct_day ||'点击量:'||CLICK_AMOUNT END aa,
       CASE WHEN rn = 2 THEN clct_day ||'点击量:'||CLICK_AMOUNT END bb,
       CASE WHEN rn = 3 THEN clct_day ||'点击量:'||CLICK_AMOUNT END cc,
       CASE WHEN rn = 4 THEN clct_day ||'点击量:'||CLICK_AMOUNT END dd,
       CASE WHEN rn = 5 THEN clct_day ||'点击量:'||CLICK_AMOUNT END ee
  FROM (SELECT A.*,
                ROW_NUMBER() OVER(PARTITION BY RPT_ID ORDER BY CLICK_AMOUNT DESC) RN
           FROM (SELECT TO_CHAR(UPDATE_TIME, 'yyyymmdd') CLCT_DAY, RPT_ID
                          --报表ID
                        , RPT_NAME
                          --报表名称
                        , COUNT(RPT_ID) CLICK_AMOUNT --,
                  -- ROw_number()over(PARTITION  BY )
                    FROM TL_T_ERPT_LOG A
                   WHERE A.UPDATE_TIME >= TRUNC(SYSDATE - 30)
                  --AND to_char(a.update_time,'D') IN ('2','3','4','5','6')
                   GROUP BY TO_CHAR(UPDATE_TIME, 'yyyymmdd'), RPT_ID, RPT_NAME) A)
 WHERE RN < = 5)
 group BY rpt_id,rpt_name
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
13763686b0     11816  package body SYS.DBMS_SQLTUNE_INTERNAL
1366ed0430         7  SYS.WRI$_ADV_SQLTUNE
134f96af70       587  package body SYS.PRVT_ADVISOR
134f96af70      2655  package body SYS.PRVT_ADVISOR
134f4c9020       241  package body SYS.DBMS_ADVISOR
13460e6488       821  package body SYS.DBMS_SQLTUNE
134c9ac050         4  anonymous block

从MODULE NAME:(DBMS_SCHEDULER)可以看到是ORACLE自己调用的程序，而从时间（22:23）可以猜测出应该和是ORACLE收集统计信息有关（ORACLE 11g每天晚上22点收集统计信息），还有从/* SQL Analyze(4508,1) */ hint也可以看出是ORACLE在试图优化这个SQL，而SYS.PRVT_ADVISOR、SYS.DBMS_ADVISOR、SYS.DBMS_SQLTUNE三个程序包也说明了这一点。
对比MOS ID 973314.1文章，发现是ORACLE的BUG（Bug 13616375），下面是MOS的相关信息。



但是MOS上并没有Solaris 10，ORACLE 11.2.0.2.0的相关补丁，要修复这个问题只能先将数据库升级到11.2.0.3.0，然后打上补丁，还好这个BUG对应用影响不大，并不一定非要修复。
期待的12C还要等到年底才会发布，不过在这也同样看到了12C的身影。

本文固定链接: https://www.dbdream.com.cn/2012/10/ora-00600-qkaffsindex5%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！