某客户一体机平台ORA-00600 : [rwoirw: check ret val]问题分析_ora-00600: 内部错误代码, 参数: [rwoirw: check ret val]

报错内容

        在某客户一体机平台，开发人员报告说一个job运行失败，调试有报错信息，ORA-00600:内部错误代码，参数：[rwoirw: check ret val]，[],[],[],[]，ORA-06512：…

报错分析

        初步判断，该报错可能是当前版本的一个bug。

        在mos上，对该报错查到了如下信息：

        predicate move-around optimization can move a wider class of predicates in a wider class of queries as compared to the standard predicate-pushdown techniques. In addition to the usual comparison and arithmetic predicates, other predicates that can be moved around are the EXISTS and NOT EXISTS clauses, the EXCEPT clause, and functional dependencies. The proposed optimization can also move predicates through aggregation. Moreover, the method can also infer new predicates when existing predicates are moved through aggregation or when certain functional dependencies are known to hold. Finally, the predicate move-around algorithm is easy to implement on top of existing query optimizers.

        什么是谓词移动？简单来说就是多个视图的查询中，将某一个视图的谓词条件Pull up出来并且push down到另一个视图中。当DML语句中的子查询存在谓词移动的情况下(尤其是包含试图)；会出现该bug。该bug影响11.2.0.4版本。 并且在如下版本中进行了修复：

解决方法

        通过调整参数或者安装补丁可以解决该问题。

        1.相关参数设置

        alter session set "_pred_move_around"=FALSE;

        2.补丁修复

        补丁修复该bug参考了客户exadata环境。

        Exadata环境Patch分析说明：

Patch  21761335     : applied on Wed Dec 16 22:19:07 CST 2015

Unique Patch ID:  19326074

Patch description:  "DATABASE PATCH FOR EXADATA (OCT2015 - 11.2.0.4.20) : (21761335)"

  Created on 21 Sep 2015, 01:49:45 hrs PST8PDT

Sub-patch  21637587; "DATABASE PATCH FOR EXADATA (Sep2015 - 11.2.0.4.19) : (21637587)"

Sub-patch  21439740; "DATABASE PATCH FOR EXADATA (AUG2015 - 11.2.0.4.18) : (21439740)"

Sub-patch  21025821; "DATABASE PATCH FOR EXADATA (JUL2015 - 11.2.0.4.17) : (21025821)"

Sub-patch  20449729; "DATABASE PATCH FOR EXADATA (APR2015 - 11.2.0.4.16) : (20449729)"

Sub-patch  20059375; "DATABASE PATCH FOR EXADATA (JAN2015 - 11.2.0.4.15) : (20059375)"

Sub-patch  19893780; "DATABASE PATCH FOR EXADATA (Dec2014 - 11.2.0.4.14) : (19893780)"

Sub-patch  19698715; "DATABASE PATCH FOR EXADATA (Nov2014 - 11.2.0.4.13) : (19698715)"

Sub-patch  19495869; "DATABASE PATCH FOR EXADATA (OCT2014 - 11.2.0.4.12) : (19495869)"

Sub-patch  19307276; "DATABASE PATCH FOR EXADATA (SEP2014 - 11.2.0.4.11) : (19307276)"

Sub-patch  19061859; "DATABASE PATCH FOR EXADATA (AUG2014 - 11.2.0.4.10) : (19061859)"

Sub-patch  18825509; "DATABASE PATCH FOR EXADATA (JUL2014 - 11.2.0.4.9) : (18825509)"

Sub-patch  18642122; "DATABASE PATCH FOR EXADATA (JUN2014 - 11.2.0.4.8) : (18642122)"

Sub-patch  18552960; "DATABASE PATCH FOR EXADATA (MAY2014 - 11.2.0.4.7) : (18552960)"

Sub-patch  18293775; "DATABASE PATCH FOR EXADATA (APR2014 - 11.2.0.4.6) : (18293775)"

Sub-patch  18136151; "DATABASE PATCH FOR EXADATA (MAR2014 - 11.2.0.4.5) : (18136151)"

Sub-patch  18006299; "DATABASE PATCH FOR EXADATA (FEB2014 - 11.2.0.4.4) : (18006299)"

Sub-patch  17943261; "DATABASE PATCH FOR EXADATA (JAN2014 - 11.2.0.4.3) : (17943261)"

Sub-patch  17741631; "DATABASE PATCH FOR EXADATA (DEC 2013 - 11.2.0.4.2) : (17741631)"

Sub-patch  17628006; "DATABASE PATCH FOR EXADATA (NOV 2013 - 11.2.0.4.1) : (17628006)"

  Bugs fixed:

    17288409, 16188701, 21051852, 16930924, 17205719, 17811429, 13640676

    18607546, 20506699, 14705949, 17816865, 17922254, 17754782, 13364795

    16934803, 17311728, 18418934, 16809786, 17284817, 17441661, 14275161

………

根因分析

        通过进一步分析，根本原因是：该bug跟应用关系较大，当子查询存在大量视图使用(或view 操作)的情况下，Oracle从11.2.0.4由于谓词移动功能的启用，会出现该错误。而当前环境确实没有安装该Patch。由于该BUG在12.1.0.1版本中已修复，因此同一环境的12.1 rac环境不受影响。