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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of MVR
Sent: Wednesday, June 13, 2007 1:32 PM
To: oracle-l@xxxxxxxxxxxxx
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


Other related posts: