Re: 10gR2 Performance sux!

Did you check what the init.ora parameters were before and after the upgrade including the hidden ones.

We upgraded our machine and the ONLY thing that changed was that the cpus where now hyperthreaded. This changed some of the hidden parameter values and we experiences a few interesting performance issues due to it.

Have you tried using Dataguard and using the sql advisors to help? Sometimes they will come up with indexes, accesses or profiles you would not have thought of. This has helped me out quite a bit.

Any time you move from one database level to another you get some gains and some losses.

Even if you test throughly before you implement you always get some surprises or at my company we call then opportunities!

Kathy

MVR <yoursraju007_at_gmail.com> wrote: 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




       
---------------------------------
Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
--
http://www.freelists.org/webpage/oracle-l