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@xxxxxxxxx> 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.

Other related posts: