10gR2 Performance sux!

Hello everyone,

Select Query has got many outer joins and it runs very good in 9i (9.2.0.5) and the same query crawls after upgrade to 10.2.0.2.0. Index are same in both the cases and statistics are good.

9i explain plan has got lots of "NESTED LOOPS OUTER" , one hash join and its selecting an index on a huge table, with AND-EQUAL.

|  11 |            NESTED LOOPS                      |
         |     1 |    90 |  2798 |       |       |


|* 12 |             INDEX RANGE SCAN                 |

T_BO_SEC_NEW_U01         |   349 | 13611 |     6 |       |       |


|* 13 |             TABLE ACCESS BY INDEX ROWID      | T_PLAN_EMP

         |     1 |    51 |  2798 |       |       |
|  14 |              AND-EQUAL                       |
         |       |       |       |       |       |


|* 15 |               INDEX RANGE SCAN               |

NU_T_PLAN_EMP_DR_ORG_ID  |       |       |       |       |       |


|* 16 |               INDEX RANGE SCAN               |

XT_PLAN_EMP_PLAN_ID      |  1885 |       |     5 |       |       |


|* 17 |            TABLE ACCESS BY GLOBAL INDEX ROWID| T_LOOKUP

         |     1 |    33 |     1 | ROWID | ROW L |


|* 18 |             INDEX UNIQUE SCAN                | SYS_C003844

         |     1 |       |       |       |       |

10g explain has got lots of HASH JOIN RIGHT OUTER, HASH JOIN OUTER and full tablescans.. Its not picking the index on big table.

|   5 |     TABLE ACCESS FULL                      | T_CURRENCY
    |   202 |  2020 |       |     2   (0)| 00:00:01 |       |       |


|*  6 |     HASH JOIN OUTER                        |

    |  2146 |   542K|       | 61589   (3)| 00:14:23 |       |       |


|*  7 |      HASH JOIN RIGHT OUTER                 |

    |  1664 |   390K|       | 59903   (3)| 00:13:59 |       |       |
|   8 |       TABLE ACCESS FULL                    | T_CURRENCY
    |   202 |  2020 |       |     2   (0)| 00:00:01 |       |       |


|*  9 |       HASH JOIN OUTER                      |

    |  1664 |   373K|       | 59901   (3)| 00:13:59 |       |       |


|* 10 |        HASH JOIN OUTER                     |

    |  1324 |   272K|       | 58215   (3)| 00:13:36 |       |       |
|  11 |         NESTED LOOPS OUTER                 |
    |  1324 |   230K|       | 58063   (3)| 00:13:33 |       |       |


|* 12 |          HASH JOIN RIGHT OUTER             |

    |  1324 |   193K|       | 57779   (3)| 00:13:29 |       |       |
|  13 |           TABLE ACCESS FULL                | T_CURRENCY
    |   202 |  4444 |       |     2   (0)| 00:00:01 |       |       |


|* 14 |           HASH JOIN                        |

    |  1324 |   165K|       | 57777   (3)| 00:13:29 |       |       |


|* 15 |            HASH JOIN                       |

    |  1324 |   112K|  3024K| 57474   (3)| 00:13:25 |       |       |


|* 16 |             INDEX RANGE SCAN               | T_BO_SEC_NEW_U01

    | 63025 |  2277K|       |   775   (1)| 00:00:11 |       |       |


|* 17 |             TABLE ACCESS FULL              | T_PLAN_EMP

    |   307K|    14M|       | 55783   (3)| 00:13:01 |       |       |

If I alter optimizer_features_enable to 9.0.1 at session level. It does get almost same explain plan and same results. But customer does not want to do that.

There is no solution yet from Oracle on Bug# 6072579 , 6068009. Is there any CBO guru out here?

I will send SQL, Explain plans and 10053 traces to personal email, if you want.

Thanks,
Raj

--
http://www.freelists.org/webpage/oracle-l