又见Bug 6316585 - 是升级还是加隐含参数，还是改SQL ?

Oracle 10g RAC ,   10.2.0.4 ,    Linux AS 5.3  .

节点1，2 中报出很多 Trace dumping is performing id=[cdmp_20100330165257]  类似信息。  执行那个SQL的
节点直接报出 错误，见后面 。 查询发现好像是一个Bug .   




如果可以的话， 修改SQL应该是最简单的方法，升级到 10.2.0.5 在目前情况下几乎不可能 （万一失败承担不了后果） ，
加入隐含参数又不是Oracle建议的 。   




Tue Mar 30 16:31:15 2010
Thread 3 advanced to log sequence 11840 (LGWR switch)
  Current log# 42 seq# 11840 mem# 0: /ocfs_ctrl_redo/mxdell/redo42_a.log
  Current log# 42 seq# 11840 mem# 1: /ocfs_data/mxdell/redo42_b.log
Tue Mar 30 16:52:14 2010
Trace dumping is performing id=[cdmp_20100330165214]
Tue Mar 30 16:52:58 2010
Trace dumping is performing id=[cdmp_20100330165257]
Tue Mar 30 16:54:00 2010
Thread 3 advanced to log sequence 11841 (LGWR switch)
  Current log# 37 seq# 11841 mem# 0: /ocfs_ctrl_redo/mxdell/redo37_a.log
  Current log# 37 seq# 11841 mem# 1: /ocfs_data/mxdell/redo37_b.log
Tue Mar 30 16:57:33 2010
Trace dumping is performing id=[cdmp_20100330165733]
Tue Mar 30 16:58:17 2010
Trace dumping is performing id=[cdmp_20100330165817]
Tue Mar 30 16:59:40 2010
Trace dumping is performing id=[cdmp_20100330165939]
Tue Mar 30 17:15:39 2010
Thread 3 advanced to log sequence 11842 (LGWR switch)
  Current log# 38 seq# 11842 mem# 0: /ocfs_ctrl_redo/mxdell/redo38_a.log
  Current log# 38 seq# 11842 mem# 1: /ocfs_data/mxdell/redo38_b.log
                                                                              



Tue Mar 30 16:25:05 2010
Thread 4 advanced to log sequence 5855 (LGWR switch)
  Current log# 43 seq# 5855 mem# 0: /ocfs_ctrl_redo/mxdell/redo43_a.log
  Current log# 43 seq# 5855 mem# 1: /ocfs_data/mxdell/redo43_b.log
Tue Mar 30 16:52:12 2010
Errors in file /u01/product/admin/mxdell/udump/mxdell4_ora_5933.trc:
ORA-07445: 出现异常错误: æ ¸å¿ƒè½¬å‚¨ [opitca()+4618] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Tue Mar 30 16:52:14 2010
Trace dumping is performing id=[cdmp_20100330165214]
Tue Mar 30 16:52:56 2010
Errors in file /u01/product/admin/mxdell/udump/mxdell4_ora_3500.trc:
ORA-07445: 出现异常错误: æ ¸å¿ƒè½¬å‚¨ [opitca()+4618] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Tue Mar 30 16:52:57 2010
Trace dumping is performing id=[cdmp_20100330165257]
Tue Mar 30 16:55:05 2010
Thread 4 advanced to log sequence 5856 (LGWR switch)
  Current log# 44 seq# 5856 mem# 0: /ocfs_ctrl_redo/mxdell/redo44_a.log
  Current log# 44 seq# 5856 mem# 1: /ocfs_data/mxdell/redo44_b.log
Tue Mar 30 16:57:32 2010
Errors in file /u01/product/admin/mxdell/udump/mxdell4_ora_5978.trc:
ORA-07445: 出现异常错误: æ ¸å¿ƒè½¬å‚¨ [opitca()+4618] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Tue Mar 30 16:57:33 2010
Trace dumping is performing id=[cdmp_20100330165733]
Tue Mar 30 16:58:15 2010
Errors in file /u01/product/admin/mxdell/udump/mxdell4_ora_6163.trc:
ORA-07445: 出现异常错误: æ ¸å¿ƒè½¬å‚¨ [opitca()+4618] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Tue Mar 30 16:58:17 2010
Trace dumping is performing id=[cdmp_20100330165817]
Tue Mar 30 16:59:38 2010
Errors in file /u01/product/admin/mxdell/udump/mxdell4_ora_6659.trc:
ORA-07445: 出现异常错误: æ ¸å¿ƒè½¬å‚¨ [opitca()+4618] [SIGSEGV] [Address not mapped to object] [0x000000000] [] []
Tue Mar 30 16:59:39 2010
Trace dumping is performing id=[cdmp_20100330165939]
Tue Mar 30 17:25:07 2010
Thread 4 advanced to log sequence 5857 (LGWR switch)




/u01/product/admin/mxdell/udump/mxdell4_ora_6163.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/product/oracle
System name:    Linux
Node name:      mxrac04
Release:        2.6.18-128.el5
Version:        #1 SMP Wed Dec 17 11:41:38 EST 2008
Machine:        x86_64
Instance name: mxdell4
Redo thread mounted by this instance: 4
Oracle process number: 84
Unix process pid: 6163, image: oracle@mxrac04
*** 2010-03-30 16:58:15.989
*** ACTION NAME) 2010-03-30 16:58:15.989
*** MODULE NAMETOAD 9.0.1.8) 2010-03-30 16:58:15.989
*** SERVICE NAMEmxdell) 2010-03-30 16:58:15.989
*** SESSION ID2632.21897) 2010-03-30 16:58:15.989
Exception signal: 11 (SIGSEGV), code: 1 (Address not mapped to object), addr: 0x0, PC: [0x2f56b22, opitca()+4618]
*** 2010-03-30 16:58:15.995
ksedmp: internal or fatal error
ORA-07445: å<87>ºç<8e>°å¼<82>常é<94><99>误: æ ¸å¿<83>转å<82>¨ [opitca()+4618] [SIGSEGV] [Address not mappe00] [] []ct] [0x00000000
Current SQL statement for this session:
/* Formatted on 2010/03/30 15:42 (Formatter Plus v4.8.8) */
SELECT sum(sys_undismantle_qty) t--,a.*, (a.spam_total_qty - a.spam_dismantle_qty
          --  ) AS spam_undismantle_qty
  FROM (SELECT DISTINCT wo.plant_code, wo.work_order AS dismantle_wo,
                        (SELECT TO_CHAR
                                   (MIN (in_station_time),
                                    'YYYY/MM/DD HH24:MI:SS'
                                   )
                           FROM wip_d_sn_master
                          WHERE dismantle_flag = 'Y' AND finish_flag = 1
                            AND work_order = wo.work_order) AS dismantle_time,
                        (wo.target_qty + wo.spam_qty) AS total_qty,
                        wo.target_qty AS sys_total_qty,
                        (SELECT COUNT (1)
                                   AS stn_dismantle_qty
                           FROM wip_d_sn_master
                          WHERE dismantle_flag = 'Y' AND finish_flag = 1
                            AND work_order = wo.work_order and wo.sfg_type = 'SYSTEM')
                                                         AS sys_dismantle_qty,
                        (SELECT COUNT (1)
                                   AS stn_undismantle_qty
                           FROM wip_d_sn_master
                          WHERE dismantle_flag = 'N' AND finish_flag = 1
                            AND work_order = wo.work_order and wo.sfg_type = 'SYSTEM')
                                                       AS sys_undismantle_qty,
                        wo.spam_qty AS spam_total_qty,
                        DECODE
                           ((SELECT SUM (dismantle_qty)
                               FROM wip_d_spam_detail
                              WHERE work_order = wo.work_order),
                            NULL, 0,
                            (SELECT SUM (dismantle_qty)
                               FROM wip_d_spam_detail
                              WHERE work_order = wo.work_order)
                           ) AS spam_dismantle_qty
                   FROM wip_d_wo_master wo
                  WHERE   wo.work_order IN (
                           SELECT DISTINCT wo_son.work_order
                                      FROM wip_d_sn_master sn_son,
                                           wip_d_wo_master wo_son
                                     WHERE wo_son.work_order =
                                                             sn_son.work_order and wo_son.sap_delete_flag = 1
                                       AND sn_son.work_order IN (
                                              SELECT DISTINCT entity_id
                                                         FROM wip_d_hold_detail
                                                        WHERE hold_remark =
                                                                   'Dismantle'
                                                          AND hold_type = 'WO')
                                       AND sn_son.work_order NOT LIKE '%CK%')
                                       ) a
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst()+31          call     ksedst1()            000000000 ? 000000001 ?
                                                   2B8EBF77ED50 ? 2B8EBF77EDB0 ?
                                                   2B8EBF77ECF0 ? 000000000 ?
ksedmp()+610         call     ksedst()             000000000 ? 000000001 ?
                                                   2B8EBF77ED50 ? 2B8EBF77EDB0 ?
                                                   2B8EBF77ECF0 ? 000000000 ?
ssexhd()+629         call     ksedmp()             000000003 ? 000000001 ?
                                                   2B8EBF77ED50 ? 2B8EBF77EDB0 ?
                                                   2B8EBF77ECF0 ? 000000000 ?




=====================================================================================

Metalink :  




Bug 6316585 - Dump / OERI from complex view merging [ID 6316585.8]   

--------------------------------------------------------------------------------

  修改时间 03-APR-2009     类型 PATCH     状态 PUBLISHED   
function getdoc2(docstr,mlstr) { pos=docstr.indexOf(':'); docid=docstr.substring(pos+1,docstr.length); tgt="_self"; if ( location.href.slice(7,12) == "webiv" || location.href.slice(7,11) == "rmsu" ) { loc="Get?WwwID=note:" + docid; } else { if ( location.href.slice(7,12) == "metal" || location.href.slice(7,12) == "suppo" ) { loc=mlstr + docid; } else { loc="http://metalink.oracle.com/metalink/plsql/"+mlstr+docid; } } window.open(loc,tgt); return; } function getdoc(docstr) { getdoc2(docstr,"showdoc?db=NOT&id="; } function taghelp(str) { getdoc2('NOTE:245840.1#'+str,"ml2_documents.showNOT?p_id="; } Bug 6316585  Dump / OERI from complex view merging
This note gives a brief overview of bug 6316585.
The content was last updated on: 03-APR-2009
Click here for details of each of the sections below.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions < 11.2  
Versions confirmed as being affected 10.2.0.3
10.2.0.4

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 10.2.0.3 Patch 19 on Windows Platforms
10.2.0.3 Patch 26 on Windows Platforms
10.2.0.5 (Server Patch Set)
11.1.0.7 (Server Patch Set)


Symptoms: Related To:
Process May Dump (ORA-7445) / Abend / Abort
Internal Error May Occur (ORA-600)
ORA-600 [evapls1]
ORA-600 [qctcte1]
Dump in or under opitca / evaopn2
Optimizer
_COMPLEX_VIEW_MERGING


Description
Type check may fail with a dump or ORA-600 when complex view
merging occurs and the select list item in the outer query block
contains arithmetic or boolean expressions.

Workaround
  Set "_complex_view_merging"=false


Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.


References
Bug:6316585 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

[ 本帖最后由 tolywang 于 2010-3-31 13:01 编辑 ]

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

转载于:http://blog.itpub.net/35489/viewspace-631271/