协助地方数据库检查问题时，在alert文件中发现了这个错误。
错误信息为：
Thu Nov 11 07:47:59 2010
Errors in file /opt/oracle/app/oracle/admin/trade/udump/trade_ora_1372824.trc:
ORA-00600: internal error code, arguments: [qkabix], [0], [], [], [], [], [], []
对应的trace文件内容为：
/opt/oracle/app/oracle/admin/trade/udump/trade_ora_1372824.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /opt/oracle/app/oracle/product/10.2.0/db_1
System name: AIX
Node name: p570a
Release: 1
Version: 6
Machine: 00CFA7B44C00
Instance name: trade
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 1372824, image: oracle@p570a
*** ACTION NAME:() 2010-11-11 07:47:59.275
*** MODULE NAME:(w3wp.exe) 2010-11-11 07:47:59.275
*** SERVICE NAME:(trade) 2010-11-11 07:47:59.275
*** SESSION ID:(330.30546) 2010-11-11 07:47:59.275
*** 2010-11-11 07:47:59.275
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [qkabix], [0], [], [], [], [], [], []
Current SQL statement for this session:
select * from(select ROWNUM RN,a.* from (
select create_date,
buyer_id,
order_code,
buyer_name,
sender_name,
medical_name,
product_name,
product_spec,
factory_name,
--source_type,
unit_price,
request_qty,
receive_qty,
send_qty,
decode(i.order_item_state,
'0',
'准备',
'1',
'发送',
'2',
'已阅读',
'3',
'已确认',
'4',
'到货中',
'5',
'关闭',
'6',
'作废',
'7',
'缺货') order_item_state
from jg_order_item i
where i.region_id in (select r.region_id from jg_region r start with r.region_id = :region_key connect by prior r.r
egion_id = r.region_father)
and exists (select 1
from jg_user_project up
where up.user_id = :user_key
and i.project_id = up.project_id)
and exists (select 1
from jg_user_hospital uh
where uh.user_id = :user_key
and uh.hospital_id = i.buyer_id)
and exists (select 1
from jg_user_region ur
where ur.user_id = :user_key
and ur.region_id = i.region_id) and i.create_date > to_date(:dtFrom, 'yyyy-mm-dd hh24:mi:ss') and i.crea
te_date < to_date(:dtTo, 'yyyy-mm-dd hh24:mi:ss') and i.sender_name like'%tzmx%' ) a where ROWNUM <= 10) where RN >= 1
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c bl ksedst1 000000000 ? 000000000 ?
ksedmp+0290 bl ksedst 104A2CD90 ?
ksfdmp+0018 bl 03F26AC4
kgerinv+00dc bl _ptrgl
kgesinv+0020 bl kgerinv 7000004DBD20C10 ? 11050A668 ?
000100000 ? 000000000 ?
FFFFFFFFFFF49C0 ?
ksesin+006c bl kgesinv 000000000 ? 000000000 ?
FFFFFFFFFFF49B0 ? 000000000 ?
FFFFFFFFFFF49B0 ?
IPRA.$qkabix+009c bl 03F25D88
qkaix+0154 bl IPRA.$qkabix 000000048 ? FFFFFFFFFFF4D70 ?
FFFFFFFFFFF4D60 ?
FFFFFFFFFFF4D68 ? 000000001 ?
000000000 ? 000000000 ?
FFFFFFFFFFF4D58 ?
qkatab+2af8 bl qkaix 1050A91B8 ? 11050A3B0 ?
FFFFFFFFFFF5510 ? 000000001 ?
FFFFFFFFFFF4D90 ? 000000001 ?
000000001 ? 000000048 ?
qkajoi+0aec bl qkatab 000000000 ? 000000000 ?
1042C2304 ? 000000000 ?
000000000 ? 000000000 ?
IPRA.$qkaqkn+08c0 bl qkajoi 000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
000000000 ? 000000000 ?
IPRA.$qkadrv+07b4 bl IPRA.$qkaqkn 000000000 ? 700000422286D20 ?
7000002BD38A570 ? 000000000 ?
IPRA.$qkadrv+0290 bl IPRA.$qkadrv 000000000 ? 11022A3C0 ?
qkadrv+006c bl IPRA.$qkadrv 1101BDBF0 ? 000000000 ?
opitca+183c bl qkadrv 110000750 ? 7000000100F07D8 ?
kksSetBindType+0c4c bl 03F250B8
kksfbc+1054 bl kksSetBindType 000000000 ? 11047A540 ?
000000000 ? 102FFFF7CE8 ?
000000000 ? 7000002C8669A90 ?
104BB8F20 ? 7000004EB1E96C0 ?
opiexe+098c bl 01F95F44
kpoal8+0edc bl opiexe FFFFFFFFFFFBA14 ? 110455240 ?
FFFFFFFFFFF9BE8 ?
opiodr+0ae0 bl _ptrgl
ttcpip+1020 bl _ptrgl
检查metalink发现和文档ID 743212.1描述的情况十分类似，堆栈错误函数信息符合度很高，版本信息也一致，这个bug发生在利用BTREE索引产生BITMAP索引扫描执行计划，需要确实一下问题SQL的执行计划：
SQL> explain plan for
2 select * from(select ROWNUM RN,a.* from (
3 select create_date,
4 buyer_id,
5 order_code,
6 buyer_name,
7 sender_name,
8 medical_name,
9 product_name,
10 product_spec,
11 factory_name,
12 --source_type,
13 unit_price,
14 request_qty,
15 receive_qty,
16 send_qty,
17 decode(i.order_item_state,
18 '0',
19 '准备',
20 '1',
21 '发送',
22 '2',
23 '已阅读',
24 '3',
25 '已确认',
26 '4',
27 '到货中',
28 '5',
29 '关闭',
30 '6',
31 '作废',
32 '7',
33 '缺货') order_item_state
34 from jg_order_item i
35 where i.region_id in (select r.region_id from jg_region r start with r.region_id = :region_key connect by prior r.region_id = r.region_father)
36 and exists (select 1
37 from jg_user_project up
38 where up.user_id = :user_key
39 and i.project_id = up.project_id)
40 and exists (select 1
41 from jg_user_hospital uh
42 where uh.user_id = :user_key
43 and uh.hospital_id = i.buyer_id)
44 and exists (select 1
45 from jg_user_region ur
46 where ur.user_id = :user_key
47 and ur.region_id = i.region_id) and i.create_date > to_date(:dtFrom, 'yyyy-mm-dd hh24:mi:ss') and i.create_date < to_date(:dtTo, 'yyyy-mm-dd hh24:mi:ss') and i.sender_name like'%tzmx%' ) a where ROWNUM <= 10) where RN >= 1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 1267879953
--------------------------------------------------------------------------------------------------------
| Id|Operation |Name |Rows |Bytes|Cost (%CPU)|Time |
--------------------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| 519| 314 (4)|00:00:04|
|* 1| VIEW | | 1| 519| 314 (4)|00:00:04|
|* 2| COUNT STOPKEY | | | | | |
|* 3| FILTER | | | | | |
| 4| NESTED LOOPS SEMI | | 1| 241| 314 (4)|00:00:04|
| 5| NESTED LOOPS SEMI | | 1| 212| 313 (4)|00:00:04|
|* 6| HASH JOIN SEMI | | 1| 205| 313 (4)|00:00:04|
|* 7| TABLE ACCESS BY INDEX ROWID |JG_ORDER_ITEM | 1| 184| 309 (4)|00:00:04|
| 8| NESTED LOOPS | | 9| 1728| 309 (4)|00:00:04|
| 9| SORT UNIQUE | | 9| 72| 2 (0)|00:00:01|
|*10| INDEX RANGE SCAN |UN_JS_USER_REGION | 9| 72| 2 (0)|00:00:01|
| 11| BITMAP CONVERSION TO ROWIDS | | | | | |
| 12| BITMAP AND | | | | | |
| 13| BITMAP CONVERSION FROM ROWIDS| | | | | |
| 14| SORT ORDER BY | | | | | |
|*15| INDEX RANGE SCAN |IDX_K_JS_MNT_CREATE_DATE|10360| | 20 (0)|00:00:01|
| 16| BITMAP CONVERSION FROM ROWIDS| | | | | |
|*17| INDEX RANGE SCAN |IDX_K_JS_MNT_REGION_ID |10360| | 32 (4)|00:00:01|
| 18| VIEW |VW_NSO_1 | 10| 130| 3 (0)|00:00:01|
|*19| CONNECT BY WITH FILTERING | | | | | |
| 20| TABLE ACCESS BY INDEX ROWID |JG_REGION | 1| 16| 1 (0)|00:00:01|
|*21| INDEX UNIQUE SCAN |PK_JG_REGION | 1| | 0 (0)|00:00:01|
|*22| HASH JOIN | | | | | |
| 23| CONNECT BY PUMP | | | | | |
| 24| TABLE ACCESS FULL |JG_REGION | 10| 80| 3 (0)|00:00:01|
|*25| INDEX UNIQUE SCAN |UN_JS_USER_PROJECT | 4| 28| 0 (0)|00:00:01|
|*26| INDEX UNIQUE SCAN |UN_JG_USER_HOSPITAL | 100| 2900| 1 (0)|00:00:01|
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=10)
3 - filter(TO_DATE(:DTFROM,'yyyy-mm-dd hh24:mi:ss') 6 - access("I"."REGION_ID"="$nso_col_1")
7 - filter("I"."SENDER_NAME" LIKE '%tzmx%')
10 - access("UR"."USER_ID"=TO_NUMBER(:USER_KEY))
15 - access("I"."CREATE_DATE">TO_DATE(:DTFROM,'yyyy-mm-dd hh24:mi:ss') AND
"I"."CREATE_DATE" 17 - access("UR"."REGION_ID"="I"."REGION_ID")
19 - access("R"."REGION_FATHER"=PRIOR "R"."REGION_ID")
21 - access("R"."REGION_ID"=TO_NUMBER(:REGION_KEY))
22 - access("R"."REGION_FATHER"=PRIOR "R"."REGION_ID")
25 - access("UP"."USER_ID"=TO_NUMBER(:USER_KEY) AND "I"."PROJECT_ID"="UP"."PROJECT_ID")
26 - access("UH"."USER_ID"=TO_NUMBER(:USER_KEY) AND "UH"."HOSPITAL_ID"="I"."BUYER_ID")
51 rows selected.
显然执行计划中存在很明显的BTREE到BITMAP索引的转换步骤：BITMAP CONVERSION FROM ROWIDS。正是这个转换导致了bug的产生。
Oracle给出的解决方法就是禁止BITMAP到BTREE的转换，通过设置隐含函数"_b_tree_bitmap_plans"为false来实现。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-680580/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-680580/
200
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
