Oracle will report "ORA-00600:[15160]" error

环境  HPUX ia64 11.23
          Oracle 10.2.03

问题：
Oracle will report "ORA-00600:[15160]" error when executing one sql on a view

Oracle will report such error when executing below SQL:
SELECT a.stepname, a.stepcategory, a.processgroup, a.processstage,
a.planname, a.plangroup, a.stepprocess
FROM v_processcontextgroup a
where PlanName like 'AG2-TO247-ASSY%';

But if changing where conditions:(It is very strange)
where PlanName like '%AG2-TO247-ASSY%'; --work
where PlanName like '%AG2-TO247%'; --work
where PlanName like 'AG2-TO247%'; --work
where PlanName like 'AG2-TO247-A%'; --Not work.

Oracle回答 -I
PROPOSED SOLUTION(S) (Please read my answers after actions:)
======================
Please apply Patch 7257160 after upgrading to 10.2.0.4
Updated:
========
We have testing environment with same version, but we don't have this problem on testing enironment when
executing same sql. it is very strange and interesting problem.

or you may use any of the following three possible workarounds:

(a) Setting star_transformation_enabled to FALSE.
Updated:
===================
Currently, the parameter star_transformation_enabled is false for our producion
environment(Problem one)

OR (b) Setting "_complex_view_merging" to FALSE.
Updated:
===================
I have tried on this suggestion, but no help on the problem(See blow):

SQL> show parameter view
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
_complex_view_merging boolean
FALSE
optimizer_secure_view_merging boolean
TRUE

SQL> connect fwstm2/guan3li3
Connected.
SQL>
SQL>
SQL> SELECT a.stepname, a.stepcategory, a.processgroup, a.processstage,
a.planname, a.plangroup, a.stepprocess
FROM v_processcontextgroup a
where PlanName like 'AG2-TO247-ASSY%';
FROM v_processcontextgroup a
*
ERROR at line 3:
ORA-00600: internal error code, arguments: [15160], [], [], [], [],

OR (c) Setting "_optimizer_push_pred_cost_based" to FALSE.
No help for this advice, problem is still persist:(See below)

SQL> show parameter _OPTIMIZER_PUSH_PRED_COST_BASED

NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
_optimizer_push_pred_cost_based boolean
FALSE

SQL> connect fwstm2/guan3li3
Connected.

SQL> SELECT a.stepname, a.stepcategory, a.processgroup, a.processstage,
a.planname, a.plangroup, a.stepprocess
FROM v_processcontextgroup a
where PlanName like 'AG2-TO247-ASSY%';
FROM v_processcontextgroup a
*
ERROR at line 3:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []

PROPOSED SOLUTION JUSTIFICATION(S)
====================================
Note 738814.1

I don't think our problem is same with the one which is mentioned in Note 738814.1,
because the parmater star_transformation_enabled is FALSE for our environment,
but the Ora-600 problem is reported.

Oracle的回答 -II
1.
Please check if explain plan statement proceeds fine.
sql> explain plan for

It can not work, will report same error message Ora-600.

SQL> explain plan for
2 SELECT a.stepname, a.stepcategory, a.processgroup, a.processstage,
3 a.planname, a.plangroup, a.stepprocess
4 FROM v_processcontextgroup a
5 where PlanName like 'AG2-TO247-ASSY%';

FROM v_processcontextgroup a
*
ERROR at line 4:
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []

2.
Please verify the sql:
open a new session
sql> alter session set "_optimizer_undo_cost_change"='10.1.0.4';
sql>

It can work if setting "_optimizer_undo_cost_change"='10.1.0.4' (session level)

3.
open a new session
sql> alter session set optimizer_features_enable='9.2.0';
sql>

It can also work if setting set optimizer_features_enable='9.2.0' (Session level)

Oracle回答-III

It seems we are hitting the bug 4712638 .
Details:
Parse of a SQL statement may dump in kkogbro()
or signal ORA-600 [15160]

Fixed on 10.2.0.4

A merge label patch is available for 10.2.0.3 , patch 7234274. Please download and apply the patch.

Updated:
=================
problem was fixed after installed the patch, thanks, so I closed th SR

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

转载于:http://blog.itpub.net/69934/viewspace-591101/