ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-600(qkabix)错误

ORA-600(qkabix)错误

原创 Linux操作系统 作者:yangtingkun 时间:2010-11-29 23:46:02 0 删除 编辑

协助地方数据库检查问题时,在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.

显然执行计划中存在很明显的BTREEBITMAP索引的转换步骤:BITMAP CONVERSION FROM ROWIDS。正是这个转换导致了bug的产生。

Oracle给出的解决方法就是禁止BITMAPBTREE的转换,通过设置隐含函数"_b_tree_bitmap_plans"false来实现。

 

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

下一篇: 安全手册总结
请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10455207