Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Primary Key seems to be harmful for performance

RE: Primary Key seems to be harmful for performance

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Mon, 13 Mar 2006 09:09:45 -0500
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2C0EFA5@EXCHANGE.corp.perceptron.com>


Gene,

Unless we are looking at different plans, I see index being used:

INDEX UNIQUE SCAN CALL_CNTR_EMPL_PK 1 1 17 (1, 5, ) in both plans, and no table scan on CALL_CNTR_EMPL table.

Like I said before, the only difference is in the order/place of the tables in hash joins.

Igor

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
genegurevich_at_discoverfinancial.com
Sent: Friday, March 10, 2006 5:57 PM
To: oracle-l_at_freelists.org
Subject: RE: Primary Key seems to be harmful for performance

Igor,

The index that makes a difference is CALL_CNTR_EMPL_PK. Is it used in the "slow" query, while in the "fast" one the table is accessed via the full table scan

thank you

Gene Gurevich
Oracle Engineering
224-405-4079  

             "Igor Neyman"

             <ineyman_at_perceptr

             on.com>
To  

<genegurevich_at_discoverfinancial.com

             03/10/2006 03:27 >, <oracle-l_at_freelists.org>

             PM
cc    

Subject

                                       RE: Primary Key seems to be
harmful 
                                       for performance

 

 

 

 

 

 





Gene,

From your execution plans (both: "fast" and "slow") it looks like Primary Key on CALL_CNTR_EMPL_MTHLY_HIST table has nothing to do with your problem - in both cases CALL_CNTR_EMPL_MTHLY_HIST accessed through "full table scan".

The difference is in tables order/place used in "nested" hash joins: the pairing of tables in hash joins is different.

I wonder, if modifying HASH_AREA_SIZE parameter would help (or PGA_AGGREGATE_TARGET, if WORKAREA_SIZE_POLICY is set to AUTO).

Igor

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
genegurevich_at_discoverfinancial.com
Sent: Thursday, March 09, 2006 3:03 PM
To: oracle-l_at_freelists.org
Subject: Re: Primary Key seems to be harmful for performance

OK.

I was initially looking for a quick hint as to what could it all mean and didn't want to clutter my Email with explain plans. However if seeing the plans would help, I'm providing them with thanks to all who replied to my Emails - Kevin, Igor, Stephen, Nuno, Wolfgang. The version of the DB is 9204. Here is the SQL:

select distinct Call_Center_Operations_Center.OPERS_CNTR_DSC c1,

       Shift_Calendar_Dimension.FULL_MTH_YR_NM1 c2 from CCSREP.OPERS_CNTR_CDE Call_Center_Operations_Center, ( select Calendar__for_Shift_Date_.DY_TM_PERD_KEY DY_TM_PERD_KEY,

   MTH_TM_PERD__for_Shift_Month_.FULL_MTH_YR_NM FULL_MTH_YR_NM1    from TELSREP.DY_TM_PERD Calendar__for_Shift_Date_,

        TELSREP.MTH_TM_PERD MTH_TM_PERD__for_Shift_Month_    where
Calendar__for_Shift_Date_.MTH_TM_PERD_KEY=MTH_TM_PERD__for_Shift_Month_. MTH_TM_PERD_KEY)
 Shift_Calenda
r_Dimension,
  ( select CALL_CNTR_EMPL.EMPL_KEY EMPL_KEY1,     CALL_CNTR_EMPL_MTHLY_HIST.SHFT_MTH_TM_PERD_KEY SHFT_MTH_TM_PERD_KEY1,
    min(CALL_CNTR_EMPL_MTHLY_HIST.BUS_UNT_CDE) BUS_UNT_CDE1     from TELSREP.CALL_CNTR_EMPL CALL_CNTR_EMPL,

         TELSREP.CALL_CNTR_EMPL_MTHLY_HIST CALL_CNTR_EMPL_MTHLY_HIST     where CALL_CNTR_EMPL_MTHLY_HIST.EMPL_KEY=CALL_CNTR_EMPL.EMPL_KEY

    group by CALL_CNTR_EMPL.EMPL_KEY,
             CALL_CNTR_EMPL_MTHLY_HIST.SHFT_MTH_TM_PERD_KEY) T3,
TELSREP.CALL_EMPL_SUM Call_Employee_Summary  where
Call_Center_Operations_Center.OPERS_CNTR_CDE in ('UT', 'PH')

   and T3.BUS_UNT_CDE1='MS'
   and Call_Employee_Summary.EMPL_KEY=T3.EMPL_KEY1    and
Call_Employee_Summary.SHFT_MTH_TM_PERD_KEY=T3.SHFT_MTH_TM_PERD_KEY1

   and
Call_Employee_Summary.OPERS_CNTR_CDE=Call_Center_Operations_Center.OPERS _CNTR_CDE
   and
Shift_Calendar_Dimension.DY_TM_PERD_KEY=Call_Employee_Summary.SHFT_DY_TM _PERD_KEY
/

Here is the "slow" explan:

1.140 SELECT STATEMENT      (65, 5915, 140)
  2.1 SORT UNIQUE     (65, 5915, 140)
    3.1 FILTER      (, , )
      4.1 SORT GROUP BY     (65, 5915, 140)
        5.1 HASH JOIN      (6468, 588588, 90)
          6.1 TABLE ACCESS FULL MTH_TM_PERD    (315, 5985, 5)
          6.2 HASH JOIN      (6468, 465696, 84)
            7.1 TABLE ACCESS FULL DY_TM_PERD    (9499, 142485, 5)
            7.2 NESTED LOOPS      (6468, 368676, 77)
              8.1 HASH JOIN      (57872144, 3009351488, 77)
                9.1 NESTED LOOPS      (19229, 769160, 28)
                  10.1 PARTITION RANGE ALL  1 15 11 (, , )
                    11.1 INDEX FAST FULL SCAN CALL_EMPL_SUM_PK 1 15 11
(269200, 6191600, 28)
                  10.2 TABLE ACCESS BY INDEX ROWID OPERS_CNTR_CDE    (1,
17, )
                    11.1 INDEX UNIQUE SCAN OPERS_CNTR_CDE_PK    (1, , )
                9.2 PARTITION RANGE ALL  1 14 15 (, , )
                  10.1 TABLE ACCESS FULL CALL_CNTR_EMPL_MTHLY_HIST 1 14
15 (42871, 514452, 44)
              8.2 INDEX UNIQUE SCAN CALL_CNTR_EMPL_PK 1 1 17 (1, 5, )


Here is the fast explain

1.141 SELECT STATEMENT      (65, 5915, 141)
  2.1 SORT UNIQUE     (65, 5915, 141)
    3.1 FILTER      (, , )
      4.1 SORT GROUP BY     (65, 5915, 141)
        5.1 HASH JOIN      (6468, 588588, 91)
          6.1 PARTITION RANGE ALL  1 14 5 (, , )
            7.1 TABLE ACCESS FULL CALL_CNTR_EMPL_MTHLY_HIST 1 14 5
(42871, 514452, 44)
          6.2 HASH JOIN      (19229, 1519091, 41)
            7.1 TABLE ACCESS FULL MTH_TM_PERD    (315, 5985, 5)
            7.2 HASH JOIN      (19229, 1153740, 35)
              8.1 TABLE ACCESS FULL DY_TM_PERD    (9499, 142485, 5)
              8.2 NESTED LOOPS      (19229, 865305, 28)
                9.1 NESTED LOOPS      (19229, 769160, 28)
                  10.1 PARTITION RANGE ALL  1 15 13 (, , )
                    11.1 INDEX FAST FULL SCAN CALL_EMPL_SUM_PK 1 15 13
(269200, 6191600, 28)
                  10.2 TABLE ACCESS BY INDEX ROWID OPERS_CNTR_CDE    (1,
17, )
                    11.1 INDEX UNIQUE SCAN OPERS_CNTR_CDE_PK    (1, , )
                9.2 INDEX UNIQUE SCAN CALL_CNTR_EMPL_PK 1 1 17 (1, 5, )

In order to switch from "slow" to "fast" I have replaced this line

 where CALL_CNTR_EMPL_MTHLY_HIST.EMPL_KEY=CALL_CNTR_EMPL.EMPL_KEY

with this one:

 where CALL_CNTR_EMPL_MTHLY_HIST.EMPL_KEY+0=CALL_CNTR_EMPL.EMPL_KEY

The size of this table is about 9000 rows if that helps.

Again, thanks for any and all help

thank you

Gene Gurevich
Oracle Engineering
224-405-4079

             Wolfgang

             Breitling

             <breitliw_at_centrex
To

             cc.com>
genegurevich_at_discoverfinancial.com

             Sent by:
cc

             oracle-l-bounce_at_f oracle-l_at_freelists.org

             reelists.org
Subject

                                       Re: Primary Key seems to be
harmful
                                       for performance

             03/09/2006 10:08

             AM





             Please respond to

             breitliw_at_centrexc

                   c.com









It would most definitely help if we had

a) the Oracle version.release.patchlevel
b) the sql
c) the two plans
d) the statistics on the tables/indexes/columns involved
e) all the session environment parameters

in the absence of that any advice/attempt to explain is just poking in the dark. Guesswork at best - like the recipes for wartremoval which involve toads, crossroads, certain moonphases and other magical ingredients. But hey go ahead and stroke your sql with a toad. Let me know if it worked.

At 04:12 PM 3/8/2006, genegurevich_at_discoverfinancial.com wrote: >I have been working to tune a SQL. It has been running OK until last week.

>Since that time its performance has degraded significantly.  I can't 
>find any changes made to the database to explain that. When I ran it a 
>day ago, the query completed in over 2 hours. After looking at the 
>results of the tkprof, I decided to disable one of the primary keys.
>After that the same query completed in 10 seconds (!). I have reenabled

>the PK and query ran for much longer (I cancelled it after 10 minues).

>I have disabled the PK and again the SQL finished in 10 sec.
>
>So it looks like PK is a problem performace-wise. How do I fix it? I 
>don't want to drop that PK; I think that it is needed to make sure 
>there is no duplicates and from the DM perspective as well. I was 
>wondering whether this is a symptom of some problem with the
statistics?
>Does anyone have any suggestions?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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






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







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



--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 13 2006 - 08:09:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US