RE: 10gR2 Performance sux!

Raj, if you can modify the application how about considering altering the code alter the session to set hash joins disabled immediately before the SQL statement runs and turning it back on immediately after the SQL statement runs. This would affect only the sessions running this specific query and only while the statement is in use.

UT1 > alter session set hash_join_enabled = false;

Session altered.

• Mark D Powell -- Phone (313) 592-5148

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MVR Sent: Wednesday, June 13, 2007 1:32 PM
To: oracle-l_at_freelists.org
Subject: 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


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