ORA-00600: qkabix

今天数据库两个节点同时出现了几个如下的报错：

ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [qkabix], [0], [], [], [], [], [], []

查看trace后在trace中找到如下的信息：

*** 2011-08-29 09:17:40.416
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [qkabix], [0], [], [], [], [], [], []
Current SQL statement for this session:
Select * from (Select chinarailoriginaltable.*,rownum as orarownum from (SELECT lf.id as claimId,lf.REPORT_ID as reportId,lf.PERSON_INSURE as personInsure,lf.NUMBER_PLATE as numberPlate,lf.POLICY_CODE as policyCode,to_char(lf.RECODE_DATE,'yyyy-MM-dd HH24:mi') as recodeDate,lf.COMPANY_NAME as companyName,lf.DEAL_COMP_NAME as dealCompName,lfs.state_name as stateName, t.cancellationtype as cancellationtype,t.opt_notion_type as optnotiontype,t.child_state_code as childstatecode,t.loss_name as lossname,t.loss_type as losstype  FROM LP_FLOW_ClAIM lf,lp_flow_state lfs,lp_flow t  ,(select distinct(id) as orgid from AQ_ZZ start with ( id = '8E2580FC0ED3E16CE0430A0C1066E16C') connect by fzzid = prior id and scbz = '0') org  where org.orgid = t.COMPANY_ID and lf.del_flag='0' and lfs.del_flag='0'  and lfs.id = t.state_id  and t.del_flag ='0' and t.claim_id = lf.id  and t.report_id like :1 and lf.RECODE_DATE >= :2 and lf.RECODE_DATE < :3 ) chinarailoriginaltable where rownum < :4 ) where orarownum >= :5
----- Call Stack Trace -----
calling              call     entry                argument values in hex     
location             type     point                (? means dubious value)    
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000000 ? FFFFFFFFFFF3FD4 ?
ksedmp+0290          bl       ksedst               104A50948 ?
ksfdmp+0018          bl       03F4C950            
kgerinv+00dc         bl       _ptrgl              
kgesinv+0020         bl       kgerinv              000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
ksesin+006c          bl       kgesinv              000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ?
IPRA.$qkabix+009c    bl       03F49CF8            
qkaix+0154           bl       IPRA.$qkabix         000000000 ? FFFFFFFFFFF4740 ?
                                                   FFFFFFFFFFF4730 ?
                                                   FFFFFFFFFFF4738 ? 000000001 ?
                                                   000000000 ? 000000000 ?
                                                   FFFFFFFFFFF4728 ?
qkatab+2af8          bl       qkaix                11044FF48 ? 000000048 ?
                                                   000000000 ? 110196000 ?
                                                   FFFFFFFFFFF4760 ?
                                                   4482442400000000 ?
                                                   10009747C ? 000000000 ?
qkajoi+0b0c          bl       qkatab               000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
IPRA.$qkaqkn+08cc    bl       qkajoi               000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
IPRA.$qkadrv+07b4    bl       IPRA.$qkaqkn         000000000 ? 7000005D58AFED8 ?
                                                   70000088570D478 ? 000000000 ?
IPRA.$qkadrv+0290    bl       IPRA.$qkadrv         000000000 ? 11022B040 ?
qkadrv+006c          bl       IPRA.$qkadrv         1101BEF90 ? 000000000 ?
opitca+183c          bl       qkadrv               11046A350 ? 110196000 ?
kksSetBindType+0c4c  bl       03F49204             。。。。。。。。

查看了网络上相关的信息，发现yangtingkun日志里面就有记录http://blog.itpub.net/post/468/509639

结合oracle官方网站的说法，这个问题主要是执行计划里面有BITMAP CONVERSION FROM ROWIDS的执行计划，而这样的执行计划就可能导致触发oracle存在的bug。

如何避免触发bug，oracle给的意见是alter session set "_b_tree_bitmap_plans"=false; 可要知道，并不是所有的BITMAP CONVERSION FROM ROWIDS都会触发这样的bug的，可能有些情况下这样的执行计划可能对性能时有帮助的。如果一棍子打死，那么会不会得不偿失？

再结合具体的语句来考虑，报的三个错都指向了同一个语句：这个语句都有相同的执行计划（因为使用了绑定变量）。

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS  22     7580                          
  VIEW  22   19 K 7580                          
    COUNT STOPKEY                                
      FILTER                                
        NESTED LOOPS  22   7 K 7580                          
          HASH JOIN  991   178 K 5598                          
            TABLE ACCESS FULL AUTOCLAIM.LP_FLOW_STATE 122   4 K 3                          
            TABLE ACCESS BY INDEX ROWID AUTOCLAIM.LP_FLOW 331   37 K 5594                          
              NESTED LOOPS  994   145 K 5594                          
                VIEW  3   99   5                          
                  HASH UNIQUE  3   204   5                          
                    CONNECT BY WITH FILTERING                                
                      TABLE ACCESS BY INDEX ROWID AQQX_BD.AQ_ZZ 1   134   2                          
                        INDEX UNIQUE SCAN AQQX_BD.PK_AQ_ZZ 1     1                          
                      NESTED LOOPS                                
                        CONNECT BY PUMP                                
                        TABLE ACCESS BY INDEX ROWID AQQX_BD.AQ_ZZ 3   204   4                          
                          INDEX RANGE SCAN AQQX_BD.INDEX_FZZID_AQZZ_FK 3     1                          
                BITMAP CONVERSION TO ROWIDS                                
                  BITMAP AND                                
                    BITMAP CONVERSION FROM ROWIDS                                
                      SORT ORDER BY                                
                        INDEX RANGE SCAN AUTOCLAIM.INDEX_LPFLOW_IDX6 8 K   132                          
                    BITMAP CONVERSION FROM ROWIDS                                
                      SORT ORDER BY                                
                        INDEX RANGE SCAN AUTOCLAIM.INDEX_LPFLOW_IDX8 8 K   327                          
          TABLE ACCESS BY INDEX ROWID AUTOCLAIM.LP_FLOW_CLAIM 1   162   2                          
            INDEX UNIQUE SCAN AUTOCLAIM.PK_LP_FLOW_CLAIM 1     1                          

 

       再查看关于BITMAP CONVERSION TO ROWIDS 的两个相关的索引，其中有一个是存在8个列，一个列为公司代码，其余7个列为日期代码，谁这么创建的，估计脑子被驴踢了。

       再就是对于涉及的表，统计信息是8月初的，而这个报错是最近几天才出现的，而这个语句应该是很久以前就上线的，会不会和表或索引的执行计划有关，尝试代入具体的值，不管是取区分度大的值还是区分度小的值，都无法获得绑定变量语句的执行计划。

       针对以上的问题，感觉有很多方法可以处理这个问题。比如，按照oracle的建议，或者创建合适的或重建相应索引，或者重新收集表的统计信息。可能都会改变语句的执行计划，那么，换来的结果就是避免触发这个bug。

        另外，越来越感觉开发部门不明白哪些语句应该使用绑定变量，哪些不应该使用绑定变量，这始终是存在隐患的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/288166/viewspace-706137/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/288166/viewspace-706137/