ORA-600(kcblasm_1)和ORA-7445(kxhfNewBuffer)錯誤

之所以將兩個錯誤合在一起發出，主要是因為造成這兩個錯誤的SQL是同一個。

 

 

第一次出現錯誤的時候報錯ORA-600(kcblasm_1)，第二次出現改成了ORA-7445錯誤，錯誤的第一個引數為kxhfNewBuffer。

Mon Aug 10 15:47:36 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20414.trc:
ORA-00600: internal error code, arguments: [kcblasm_1], [102], [], [], [], [], [], []
Mon Aug 10 15:47:37 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20414.trc:
ORA-07445: exception encountered: core dump [0000000101D51BB4] [SIGSEGV] [Address not mapped to object] [0x3032313300043148] [] []
ORA-00600: internal error code, arguments: [kcblasm_1], [102], [], [], [], [], [], []
Mon Aug 10 15:47:38 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20414.trc:
ORA-07445: exception encountered: core dump [0000000101D51BB4] [SIGSEGV] [Address not mapped to object] [0x3032313300043148] [] []
ORA-07445: exception encountered: core dump [0000000101D51BB4] [SIGSEGV] [Address not mapped to object] [0x3032313300043148] [] []
ORA-00600: internal error code, arguments: [kcblasm_1], [102], [], [], [], [], [], []
Mon Aug 10 15:47:40 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20414.trc:
ORA-07445: exception encountered: core dump [0000000101D51BB4] [SIGSEGV] [Address not mapped to object] [0x3032313300043148] [] []
ORA-07445: exception encountered: core dump [0000000101D51BB4] [SIGSEGV] [Address not mapped to object] [0x3032313300043148] [] []
ORA-07445: exception encountered: core dump [0000000101D51BB4] [SIGSEGV] [Address not mapped to object] [0x3032313300043148] [] []
ORA-00600: internal error code, arguments: [kcblasm_1], [102], [], [], [], [], [], []
Mon Aug 10 16:19:28 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []
Mon Aug 10 16:19:28 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []
Mon Aug 10 16:19:29 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []
Mon Aug 10 16:19:31 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []
Mon Aug 10 16:19:32 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []
Mon Aug 10 16:19:34 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []
Mon Aug 10 16:19:36 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []
Mon Aug 10 16:19:39 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []
Mon Aug 10 16:19:41 2009
Errors in file /data/oracle/ora92/admin/data01/udump/data01_ora_20756.trc:
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kghasp1], [0xFFFFFFFF799800E8], [], [], [], [], [], []
ORA-07445: exception encountered: core dump [0000000101919BD0] [SIGSEGV] [Address not mapped to object] [0x000000210] [] []

兩個錯誤的間隔時間很短，而且都是同一個SQL語句所引起。

由於以前並沒有出現過這個錯誤，而最近程式又修改了SQL語句，基本上可以確定問題就是SQL語句導致的。

由於SQL語句比較長，而且和業務有關，這裡就不貼出來了。檢查SQL語句，發現可能導致問題的部分是SQL的WHERE條件中，包含了下列的寫法：

 and (b.ready_flag is null
  or b.ready_flag in
  (
   select r.ready_type from cat_zone_product_ready r 
   where r.plat_id ='FR20T0000020000000000061'
   and r.enable_flag='1'
  )
  ) 

這種用OR關聯起來的子查詢，本身就容易引發錯誤。

由於cat_zone_product_ready表中plat_id和ready_type是唯一的，因此將上面的查詢改成了外連線的方式。

將SQL簡化後一點，來說明問題，原始的寫法為：

SQL> select count(*)
  2  from (
  3   select a.id, a.trade_name, a.remark
  4   from cat_product a, cat_drug b
  5   where a.medical_id = b.id
  6   and a.enable_flag = '1' 
  7   and (b.ready_flag is null
  8    or b.ready_flag='' 
  9    or b.ready_flag in
 10     (
 11      select r.ready_type from cat_zone_product_ready r 
 12      where r.plat_id ='FR20T0000020000000000061'
 13      and r.enable_flag='1'
 14     )
 15    ) 
 16  );

  COUNT(*)
----------
    105476

修改為：

SQL> select count(*)
  2  from (
  3     select a.id, a.trade_name, a.remark
  4     from cat_product a, cat_drug b, cat_zone_product_ready r
  5     where a.medical_id = b.id
  6     and a.enable_flag = '1' 
  7     and b.ready_flag = r.ready_type (+)
  8     and r.plat_id (+)='FR20T0000020000000000061'
  9     and r.enable_flag (+) ='1'
 10     and ((r.id is not null) or (r.id is null and b.ready_flag is null))
 11  );

  COUNT(*)
----------
    105476

根據上面的方法修改了程式中的SQL語句，觀察了半個月左右，這個錯誤沒有再次出現，看來透過修改SQL的方式避開了這個錯誤。