Optimizer problem Oracle R1 versus R2

[RedInTheCorner]

I have two different exucution plan for same query, first one runs in 12 sec and the other run in 0.15 sec. Basic difference is one database is 10gR1 and the other one is 10gR2, I encounter that problem in every queries that include YH_HASTA_YATIS_SURECLERI VIEW, it is a view with analytic queries.


this is execution plan for This is 10gR1:

SELECT STATEMENT, GOAL = ALL_ROWS Cost=116127 Cardinality=288472 Bytes=178852640
SORT ORDER BY Cost=116127 Cardinality=288472 Bytes=178852640
HASH JOIN Cost=78345 Cardinality=288472 Bytes=178852640
NESTED LOOPS OUTER Cost=11 Cardinality=1373 Bytes=282838
HASH JOIN RIGHT OUTER Cost=10 Cardinality=1373 Bytes=87872
TABLE ACCESS FULL Object owner=SAMSUN Object name=YH_YATAK_ODALARI Cost=3 Cardinality=1 Bytes=26
TABLE ACCESS FULL Object owner=SAMSUN Object name=YH_YATAKLAR Cost=6 Cardinality=1373 Bytes=52174
TABLE ACCESS BY INDEX ROWID Object owner=SAMSUN Object name=MEKANLAR Cost=0 Cardinality=1 Bytes=142
INDEX UNIQUE SCAN Object owner=SAMSUN Object name=PK_MEKANLAR Cost=0 Cardinality=1
VIEW Object owner=SAMSUN Object name=YH_HASTA_YATIS_SURECLERI Cost=78330 Cardinality=288472 Bytes=119427408
WINDOW SORT Cost=78330 Cardinality=288472 Bytes=161255848
VIEW Object owner=SYS Cost=44203 Cardinality=288472 Bytes=161255848
SORT UNIQUE Cost=44203 Cardinality=288472 Bytes=182025832
HASH JOIN Cost=5712 Cardinality=288472 Bytes=182025832
TABLE ACCESS FULL Object owner=SAMSUN Object name=YH_YATIS_GRUP_STATU_ILISKI Cost=3 Cardinality=8 Bytes=56
HASH JOIN Cost=5705 Cardinality=252413 Bytes=157505712
TABLE ACCESS FULL Object owner=SAMSUN Object name=YH_HAREKET_TURLERI Cost=3 Cardinality=15 Bytes=345
HASH JOIN Cost=5699 Cardinality=252413 Bytes=151700213
TABLE ACCESS FULL Object owner=SAMSUN Object name=YH_HAREKET_TIPLERI Cost=3 Cardinality=15 Bytes=1275
MERGE JOIN Cost=5692 Cardinality=252413 Bytes=130245108
TABLE ACCESS BY INDEX ROWID Object owner=SAMSUN Object name=YH_HASTA_YATIS_HAREKETLERI Cost=3948 Cardinality=252411 Bytes=103236099
INDEX FULL SCAN Object owner=SAMSUN Object name=NDX_YH_HYHAREKETHASTAYATISNO Cost=694 Cardinality=252411
SORT JOIN Cost=1744 Cardinality=66489 Bytes=7114323
TABLE ACCESS FULL Object owner=SAMSUN Object name=YH_HASTA_YATIS Cost=127 Cardinality=66489 Bytes=7114323


And this is execution plan for This is 10gR2:

SELECT STATEMENT, GOAL = ALL_ROWS 16 3 1365
NESTED LOOPS OUTER 16 3 1365
NESTED LOOPS OUTER 16 3 1287
NESTED LOOPS 15 3 1269
VIEW DIYARX YH_HASTA_YATIS_SURECLERI 12 3 1215
WINDOW SORT 12 3 1677
VIEW SYS 11 3 1677
HASH UNIQUE 11 3 435
HASH JOIN 10 3 435
NESTED LOOPS 6 2 276
NESTED LOOPS 5 2 240
NESTED LOOPS 4 2 194
TABLE ACCESS BY INDEX ROWID DIYARX YH_HASTA_YATIS 2 1 31
INDEX UNIQUE SCAN DIYARX PK_YHHASTAYATIS 1 1
TABLE ACCESS BY INDEX ROWID DIYARX YH_HASTA_YATIS_HAREKETLERI 2 2 132
INDEX RANGE SCAN DIYARX NDX_YH_HYHAREKETHASTAYATISNO 1 2
TABLE ACCESS BY INDEX ROWID DIYARX YH_HAREKET_TIPLERI 1 1 23
INDEX UNIQUE SCAN DIYARX PK_YHHAREKETTIPLERI 0 1
TABLE ACCESS BY INDEX ROWID DIYARX YH_HAREKET_TURLERI 1 1 18
INDEX UNIQUE SCAN DIYARX PK_YHHAREKETTURLERI 0 1
TABLE ACCESS FULL DIYARX YH_YATIS_GRUP_STATU_ILISKI 3 8 56
TABLE ACCESS BY INDEX ROWID DIYARX YH_YATAKLAR 1 1 18
INDEX UNIQUE SCAN DIYARX PK_YHYATAKLAR 0 1
TABLE ACCESS BY INDEX ROWID DIYARX YH_YATAK_ODALARI 1 1 6
INDEX UNIQUE SCAN DIYARX PK_YHYATAKODALARI 0 1
TABLE ACCESS BY INDEX ROWID DIYARX MEKANLAR 0 1 26
INDEX UNIQUE SCAN DIYARX PK_MEKANLAR 0 1

Thanks

 

[jaxtell]

If both databases have the all the same info, and the stats are up to date, then I'd suggest using hints to get it to execute the way it does on r2.

 

[RedInTheCorner]

Thanks,
I tried it before it didn't solve my problem. I think problem reason was in 10gR1 oracle first sort analytic queries in view and then filter them. However in 10gR2 oracle first filter with view and then sort it. I solved problem by writing a query with out analytic functions