一套windows上的11.1.0.7系统，用户在查询时出现ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [], [], [], [], []错误如下:
Dump continued from file: c:\app\administrator\diag\rdbms\dbuat\dbuat\trace\dbuat_ora_544.trc ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], [], [], [], [], [] ========= Dump for incident 16131 (ORA 600 [15160]) ======== *** 2010-07-20 15:49:20.015 ----- Current SQL Statement for this session (sql_id=3vx5wh2859qy4) ----- SELECT * FROM ACVW_ACDCBIRD WHERE CUST_AC_NO = NVL(:B5 , CUST_AC_NO) AND BRANCH_CODE = :B4 AND ACCOUNT_CLASS = NVL(:B3 , ACCOUNT_CLASS) AND CUST_NO = NVL(:B2 , CUST_NO) AND CCY = NVL(:B1 , CCY) ----- PL/SQL Call Stack ----- object line object handle number name 4648BA6C 811 package body FCUAT.ACPKS_FCJ_ACDCBIRD 4648BA6C 895 package body FCUAT.ACPKS_FCJ_ACDCBIRD 4648BA6C 199 package body FCUAT.ACPKS_FCJ_ACDCBIRD 4648BA6C 122 package body FCUAT.ACPKS_FCJ_ACDCBIRD 4648E458 13 anonymous block 4648F520 161 package body FCUAT.STPKS_FIDPKG_WRAPPER 464953BC 1940 package body FCUAT.STPKS_FCMAINT_SERVICE 464B8DE0 164 package body FCUAT.GWPKS_SERVICE_ROUTER 464B8DE0 1549 package body FCUAT.GWPKS_SERVICE_ROUTER 464B8DE0 1718 package body FCUAT.GWPKS_SERVICE_ROUTER 464B96E0 1 anonymous block ----- Call Stack Trace ----- PGOSF60__ksfdmp dbgexPhaseII dbgexProcessError dbgeExecuteForError该ORA-600[15160]内部错误已知可能由以下Bug引起:
1) Bug:8295719 - ORA-600 [15160] running query with FULL OUTER join Fixed-Releases: B201 Tags: ANSI CBO OERI REDISCOVERY INFORMATION: If a query with nested views and Full Outer Join fails during parsing in Oracle11g with ORA-600 [15160] and Join Predicate Push Down (JPPD) is taking place and one of the nested view query blocks in the 10053 trace has a very high value for cost in the chosen plan (in section CBQT Join Predicate Push Down Additional Phase) then you may have encountered this bug. WORKAROUND: _optimizer_push_pred_cost_based = false 2) Bug 7370515 - ORA-600[15160]: INTERNAL ERROR CODE REDISCOVERY INFORMATION: If you encountered ORA-600[15160] error when UNION/UNION ALL view is on the right side of null aware anti join, it could be this bug. WORKAROUND: Set "_optimizer_null_aware_antijoin"=false 3) Bug 9826143 - UNION QUERY WITH OUTER JOIN AND GROUP BY CLAUSE FAILS WITH ORA-600 [15160] (36) >>>>>>>>>> Bug 9213751 - SQL GETS ORA-600 [15160] WITH _OPTIMIZER_PUSH_PRED_COST_BASED=TRUE (11) From trace: [_optimizer_push_pred_cost_based = true] 4) Bug 8947490 - ORA-600[15160] OCCURS WHEN USING SQL TUNING ADVISOR REDISCOVERY INFORMATION: a query with full outer joined union view produced ora-[15160] most likely during sqltune /index advisor.可以从bug描述中了解到这是一个优化器相关的bug，一般与优化器相关的bug都可以通过关闭优化器的部分特性来workaround。针对ORA-600[15160]内部错误，我们可以尝试设置以下2个隐藏参数以绕过问题:
1) SQl> Alter session set "_optimizer_null_aware_antijoin"=false; /* to disable null aware anti join */ 2) SQl> Alter session set "_OPTIMIZER_PUSH_PRED_COST_BASED" =false ; /*WORKAROUND: disable cost based push predicate */
以上多个bug据Oracle官方说法均已在11.2.0.1版本中修复了。
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277593
