In 10g - Full Table Scan In Sql Statement With Connect By

Hi

We upgraded from 9i (9.2.0.6) to 10g (10.2.0.1)

In 10g, the DB is doing Full Table Scans by the CONNECT BY START sql statements.

I have tried the following:
1) set "optimizer_features_enable"= '9.2.0' - did not help

2) set "optimizer_features_enable"= '10.2.0.1'
and set "_optimizer_rownum_pred_based_fkr"= false
- did not help

3) set "_OLD_CONNECT_BY_ENABLED"=TRUE
and I got the following error:
ERROR at line 33:
ORA-00600: internal error code, arguments: [12426], [69], [], [], [], [], [], []

- did not help

In Metalink, I see that Oracle is aware that in 10g
there is a bug (BUG 5211863) Poor plan for Connect By.
The bug reports that in 10.2.0.4 the bug will be resolved.
However 10.2.0.4 does not have any release yet.


Did anyone encounter the same problem in 10g (10.2.0.1)
and find a solution (that the DB will not do Full Table Scans) ?

Hi

What is the query and the plans like before and after upgrade.

regards
Hrishy

Hi,

I will upload a trace file:
1) cormayu3_ora_5476.txt - trace file in 9i (9.2.0.6)
2) core101_ora_768.txt - trace file in 10g (10.2.0.1)

The elapsed time in 9i : less than 1 second
The elapsed time in 10g: 48 seconds


For this Oracle 10g bug we are looking for a database general solution and not to have to change every sql with CONNECT BY START..