In 10g - Full Table Scan In Sql Statement With Connect By
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Jul 2004
    Posts
    21

    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) ?

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

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

    regards
    Hrishy

  3. #3
    Join Date
    Jul 2004
    Posts
    21
    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..
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width