ORA-00600[Qkabix] Error Caused by a Particular Query [ID 743212.1]

环境： AIX + ORACLE10.2.0.4  2节点 RAC 

 

现象： alert_caprod2.log  中频繁出现 ORA-00600: internal error code, arguments: [qkabix], [0], [], [], [], [], [], []

 

Sun Sep 26 11:12:41 2010
Errors in file /home/oracle/app/admin/caprod/udump/caprod2_ora_283514.trc:
ORA-00600: internal error code, arguments: [qkabix], [0], [], [], [], [], [], []
Sun Sep 26 11:12:43 2010
Trace dumping is performing id=[cdmp_20100926111243]
Sun Sep 26 11:18:33 2010
Trace dumping is performing id=[cdmp_20100926111833]
Sun Sep 26 12:54:06 2010
Trace dumping is performing id=[cdmp_20100926125

 

 

原因：

在caprod2_ora_283514.trc中发现引起这个bug的sql执行计划有

BITMAP CONVERSION FROM ROWIDS 而这些表中根本没有BITMAP索引。

 

测试结果：

同样的sql在oracle 10.2.0.4  单机+aix上执行计划没问题。

在oracle10.2.0.4   4节点rac+ linux 上执行计划也没问题。

只有在AIX + ORACLE10.2.0.4  2节点 RAC 中的执行计划有问题。

 

临时解决方案：

alter system set "_b_tree_bitmap_plans"=false;

 

引发此bug的sql

 

ORA-00600: internal error code, arguments: [qkabix], [0], [], [], [], [], [], []
Current SQL statement for this session:

select * from ( select wbusinessc0_.id as id0_0_, prpldeflos1_.id as id321_1_, prpldeflos3_.id as id324_2_, wbusinessc0_.prepnodeid as prepnodeid0_0_, wbusinessc0_.nodeid as nodeid0_0_, wbusinessc0_.transactNodeId as transact4_0_0_, wbusinessc0_.processid as processid0_0_, wbusinessc0_.taskid as taskid0_0_, wbusinessc0_.businessno as businessno0_0_, wbusinessc0_.riskcode as riskcode0_0_, wbusinessc0_.inkey as inkey0_0_, wbusinessc0_.indate as indate0_0_, wbusinessc0_.pendingdate as pending11_0_0_, wbusinessc0_.outdate as outdate0_0_, wbusinessc0_.state as state0_0_, wbusinessc0_.comcode as comcode0_0_, wbusinessc0_.usercode as usercode0_0_, wbusinessc0_.handlerrole as handler16_0_0_, wbusinessc0_.handleruser as handler17_0_0_, wbusinessc0_.prepcomcode as prepcom18_0_0_, wbusinessc0_.prepuser as prepuser0_0_, wbusinessc0_.preptaskid as preptaskid0_0_, wbusinessc0_.businessid as businessid0_0_, wbusinessc0_.businesstable as busines22_0_0_, wbusinessc0_.flag as flag0_0_, wbusinessc0_.valid as valid0_0_, wbusinessc0_.moveid as moveid0_0_, wbusinessc0_.casetag as casetag0_0_, wbusinessc0_.acceptDate as acceptDate0_0_, wbusinessc0_.acceptFlag as acceptFlag0_0_, wbusinessc0_.cancelState as cancelS29_0_0_, wbusinessc0_.cancelDate as cancelDate0_0_, wbusinessc0_.resumeDate as resumeDate0_0_, wbusinessc0_.cancelUser as cancelUser0_0_, wbusinessc0_.businessType as busines33_0_0_, wbusinessc0_.makeCom as makeCom0_0_, wbusinessc0_.mainNo as mainNo0_0_, wbusinessc0_.autoTaskFlag as autoTas36_0_0_, wbusinessc0_.businessNodeId as busines37_0_0_, wbusinessc0_.systemCode as systemCode0_0_, wbusinessc0_.rightGroup as rightGroup0_0_, prpldeflos1_.version as version321_1_, prpldeflos1_.flag as flag321_1_, prpldeflos1_.currency as currency321_1_, prpldeflos1_.remark as remark321_1_, prpldeflos1_.comcode as comcode321_1_, prpldeflos1_.policyno as policyno321_1_, prpldeflos1_.riskcode as riskcode321_1_, prpldeflos1_.makecom as makecom321_1_, prpldeflos1_.claimno as claimno321_1_, prpldeflos1_.registno as registno321_1_, prpldeflos1_.validflag as validflag321_1_, prpldeflos1_.losstype as losstype321_1_, prpldeflos1_.lossfeetype as lossfee14_321_1_, prpldeflos1_.lflag as lflag321_1_, prpldeflos1_.underwriteenddate as underwr16_321_1_, prpldeflos1_.underwriteflag as underwr17_321_1_, prpldeflos1_.lossstate as lossstate321_1_, prpldeflos1_.prplcheckcarid as prplche19_321_1_, prpldeflos1_.mercyflag as mercyflag321_1_, prpldeflos1_.underwriteopinion as underwr21_321_1_, prpldeflos1_.prplthirdpartid as prplthi22_321_1_, prpldeflos1_.firstSiteFlag as firstSi23_321_1_, prpldeflos1_.losspart as losspart321_1_, prpldeflos1_.quotesumlossfee as quotesu25_321_1_, prpldeflos1_.cetainlosstype as cetainl26_321_1_, prpldeflos1_.sumlossfee as sumlossfee321_1_, prpldeflos1_.customeridentify as custome28_321_1_, prpldeflos1_.chgdiscountrate as chgdisc29_321_1_, prpldeflos1_.sumremnant as sumremnant321_1_, prpldeflos1_.customerremark as custome31_321_1_, prpldeflos1_.sumverilossfee as sumveri32_321_1_, prpldeflos1_.insuredname as insured33_321_1_, prpldeflos1_.inputtime as inputtime321_1_, prpldeflos1_.handlercode as handler35_321_1_, prpldeflos1_.underwritecode as underwr36_321_1_, prpldeflos1_.sumverirescuefee as sumveri37_321_1_, prpldeflos1_.sumveriremnant as sumveri38_321_1_, prpldeflos1_.quoteoperatorcode as quoteop39_321_1_, prpldeflos1_.repairfactorycode as repairf40_321_1_, prpldeflos1_.handlercode2 as handler41_321_1_, prpldeflos1_.finalhandlercode as finalha42_321_1_, prpldeflos1_.underwritename as underwr43_321_1_, prpldeflos1_.underwritecom as underwr44_321_1_, prpldeflos1_.quotesumchgcompfee as quotesu45_321_1_, prpldeflos1_.sumchgcompfee as sumchgc46_321_1_, prpldeflos1_.summanagefee as summana47_321_1_, prpldeflos1_.summaterialfee as summate48_321_1_, prpldeflos1_.sumrepairfee as sumrepa49_321_1_, prpldeflos1_.sumrescuefee as sumresc50_321_1_, prpldeflos1_.handlername as handler51_321_1_, prpldeflos1_.defsite as defsite321_1_, prpldeflos1_.exgratiafee as exgrati53_321_1_, prpldeflos1_.exgratiaremark as exgrati54_321_1_, prpldeflos1_.repairfactoryname as repairf55_321_1_, prpldeflos1_.deflossremark as defloss56_321_1_, prpldeflos1_.underwriteremark as underwr57_321_1_, prpldeflos1_.senddate as senddate321_1_, prpldeflos1_.estimateddate as estimat59_321_1_, prpldeflos1_.losslevel as losslevel321_1_, prpldeflos1_.examfactorycode as examfac61_321_1_, prpldeflos1_.examfactoryname as examfac62_321_1_, prpldeflos1_.repairfactorytype as repairf63_321_1_, prpldeflos1_.handlername2 as handler64_321_1_, prpldeflos1_.deflossdate as defloss65_321_1_, prpldeflos1_.finalhandlername as finalha66_321_1_, prpldeflos1_.underwritelevel as underwr67_321_1_, prpldeflos1_.compenbackflag as compenb68_321_1_, prpldeflos1_.compenopinion as compeno69_321_1_, prpldeflos1_.compenbackdate as compenb70_321_1_, prpldeflos1_.compenoperator as compeno71_321_1_, prpldeflos1_.verichgdiscountrate as verichg72_321_1_, prpldeflos1_.sumverichgcompfee as sumveri73_321_1_, prpldeflos1_.sumverirepairfee as sumveri74_321_1_, prpldeflos1_.sumverimanagefee as sumveri75_321_1_, prpldeflos1_.sumverimaterialfee as sumveri76_321_1_, prpldeflos1_.quotechgdiscountrate as quotech77_321_1_, prpldeflos1_.quotesumrepairfee as quotesu78_321_1_, prpldeflos1_.quotesummanagefee as quotesu79_321_1_, prpldeflos1_.quotesummaterialfee as quotesu80_321_1_, prpldeflos1_.sumquoteremnant as sumquot81_321_1_, prpldeflos1_.quoteoperatorname as quoteop82_321_1_, prpldeflos1_.quotemakecom as quotema83_321_1_, prpldeflos1_.quotepassdate as quotepa84_321_1_, prpldeflos1_.quoteflag as quoteflag321_1_, prpldeflos1_.thirdpartflag as thirdpa86_321_1_, prpldeflos1_.thirdpartsource as thirdpa87_321_1_, prpldeflos3_.flag as flag324_2_, prpldeflos3_.startdate as startdate324_2_, prpldeflos3_.actualvalue as actualva4_324_2_, prpldeflos3_.remark as remark324_2_, prpldeflos3_.insuredflag as insuredf6_324_2_, prpldeflos3_.operatorcode as operator7_324_2_, prpldeflos3_.useyears as useyears324_2_, prpldeflos3_.registno as registno324_2_, prpldeflos3_.carkindcode as carkind10_324_2_, prpldeflos3_.validflag as validflag324_2_, prpldeflos3_.lossitemtype as lossite12_324_2_, prpldeflos3_.enddate as enddate324_2_, prpldeflos3_.purchaseprice as purchas14_324_2_, prpldeflos3_.firstsiteflag as firstsi15_324_2_, prpldeflos3_.ciindemduty as ciindem16_324_2_, prpldeflos3_.licensetype as license17_324_2_, prpldeflos3_.licensecolorcode as license18_324_2_, prpldeflos3_.gearboxtype as gearbox19_324_2_, prpldeflos3_.gastype as gastype324_2_, prpldeflos3_.vinno as vinno324_2_, prpldeflos3_.modelcode as modelcode324_2_, prpldeflos3_.modelname as modelname324_2_, prpldeflos3_.prplthirdpartyid as prplthi24_324_2_, prpldeflos3_.licenseno as licenseno324_2_, prpldeflos3_.brandname as brandname324_2_, prpldeflos3_.frameno as frameno324_2_, prpldeflos3_.engineno as engineno324_2_, prpldeflos3_.inputtime as inputtime324_2_, prpldeflos3_.carowner as carowner324_2_, prpldeflos3_.starthour as starthour324_2_, prpldeflos3_.endhour as endhour324_2_, prpldeflos3_.clausetype as clausetype324_2_, prpldeflos3_.carcolorcode as carcolo34_324_2_, prpldeflos3_.enrolldate as enrolldate324_2_, prpldeflos3_.loaddangerflag as loaddan36_324_2_, prpldeflos3_.modifiedind as modifie37_324_2_, prpldeflos3_.carkindfrom as carkind38_324_2_, prpldeflos3_.losscarkindcode as losscar39_324_2_, prpldeflos3_.losscarkindname as losscar40_324_2_, prpldeflos3_.rundistance as rundist41_324_2_, prpldeflos3_.lossflag as lossflag324_2_, prpldeflos3_.insurecomcode as insurec43_324_2_, prpldeflos3_.insurecomname as insurec44_324_2_, prpldeflos3_.dutypercent as dutyper45_324_2_, prpldeflos3_.lastmodifydate as lastmod46_324_2_, prpldeflos3_.lasthanderpost as lasthan47_324_2_, prpldeflos3_.lasthandercode as lasthan48_324_2_ from PrpLbpmMain wbusinessc0_, prpldeflossmain prpldeflos1_, prplbpmcompany prplbpmcom2_, prpldeflossthirdparty prpldeflos3_, PrpLbpmNodeType wbusinesss4_ where wbusinessc0_.nodeid=wbusinesss4_.id and prpldeflos1_.id=wbusinessc0_.businessid and wbusinessc0_.valid=1 and prpldeflos3_.id=prpldeflos1_.prplthirdpartid and (wbusinessc0_.riskcode like '05%') and wbusinesss4_.nodename='LossApprCarOne' and wbusinessc0_.taskid=prplbpmcom2_.bpmmainid and prplbpmcom2_.valid='1' and (prplbpmcom2_.comcode1 in ('40000000' , '40001001' , '40008007' , '40008008' , '40000001' , '40000010' , '40000020' , '40000030' , '40000040' , '40008001' , '40008002' , '40008003' , '40008004' , '40008005')) and (wbusinessc0_.usercode='4000004002' or wbusinessc0_.usercode='EMPTY') and (prpldeflos1_.underwriteflag like '0%' or prpldeflos1_.underwriteflag like '2%' or prpldeflos1_.underwriteflag like '1%' or prpldeflos1_.underwriteflag like '3%') and wbusinessc0_.indate>=to_date('2010-09-01', 'YYYY-MM-DD') and wbusinessc0_.indate<=to_date('2010-09-27', 'YYYY-MM-DD') and wbusinessc0_.state='2' and wbusinessc0_.cancelState='0' ) where rownum <= :1

 

 

以下是metalink上关于此bug的描述： 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.5 - Release: 10.2 to 10.2

Symptoms

The following internal error is reported in the alert log:
    ORA-00600: internal error code, arguments: [qkabix], [0], [], [], [], [], [], []

The error is reported running a Select statement.

The call stack looks like:
   qkabix qkaix qkatab qkajoi qkaqkn qkadrv opitca kksFullTypeCheck rpiswu2 kksSetBindType kksfbc
   kkspfda kpodny kpoal8

Cause

The problem could be related to a CBO ( Cost Based Optimizer ) issue when a query uses a bitmap access paths for b-tree indexes.

It was reported in Bug 5945798 but could not be confirmed.

Solution

One possible workaround to avoid the error is to disable the optimizer to produce bitmap plans for B-Tree indexes, even if there is no bitmap index anywhere in sight.

- at session level:
     sql> alter session set "_b_tree_bitmap_plans"=false;

- or at system level:
     sql> alter system set "_b_tree_bitmap_plans"=false;

- or in the init.ora/spfile:
     _b_tree_bitmap_plans = false

References

BUG:5945798 - ORA-00600 [QKABIX] WHILE RUNNING A SELECT STATEMENT