ORA-00600: internal error code, arguments: [15160]

一套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版本中修复了。


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *