aix5.3+10.2.0.2 RAC

昨天某一生产系统再次遭遇这个错误，但和以前的BUG不 太一样，

经过分析解决的方法也不太一样，

[@more@]

Bug 4648181 OERI [15160] possible from pushed predicates

This note gives a brief overview of bug 4648181.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions < 11
Versions confirmed as being affected	10.1.0.4 10.1.0.5 10.2.0.1 10.2.0.2
Platforms affected	Generic (all / most platforms affected)

It is believed to be a regression in default behaviour thus:
Regression introduced in 10.1.0.4

Fixed:

This issue is fixed in

10.2.0.3 (Server Patch Set) 11g (Future version)

Description

This problem is introduced in 10.1.0.4.

Queries using pushed join predicates can fail with ORA-600 [15160].

eg:
  create table bug_test(c1 varchar2(10), c2 varchar2(10));

  select 1 from
   (select c1,null c2 from bug_test
    union all
    select c1,c2 from bug_test
   ) v1,
   (select c1,c2 from bug_test
    union all
    select c1,c2 from bug_test
   ) v2
   where v2.c1=v1.c1 and v2.c2=v1.c2
  ;


Workaround:
  Set _optimizer_cost_based_transformation = off
OR
  Set _optimizer_push_pred_cost_based = false

Bug 5172444 OERI[15160] on complex query with joinback elimination

This note gives a brief overview of bug 5172444.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions < 11
Versions confirmed as being affected	10.2.0.2
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

10.2.0.3 (Server Patch Set) 11g (Future version)

Description

ORA-600 [15160] errors are possible during optimization of queries using star
transformation if there are multiple single row tables, and joinback
elimination is possible.

An optimizer trace will show the order of single row tables at the
start of the join order has changed after joinback elimination.

Workaround:
  Set event 10162 level 1 to disable joinback elimination.