RE: Primary Key seems to be harmful for performance

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxxxxxxxxxxx
Sent: Friday, March 10, 2006 5:57 PM
To: oracle-l@xxxxxxxxxxxxx
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@perceptr

             on.com>
To 
 
<genegurevich@xxxxxxxxxxxxxxxxxxxxx 
             03/10/2006 03:27          >, <oracle-l@xxxxxxxxxxxxx>

             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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
genegurevich@xxxxxxxxxxxxxxxxxxxxx
Sent: Thursday, March 09, 2006 3:03 PM
To: oracle-l@xxxxxxxxxxxxx
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@centrex
To
             cc.com>
genegurevich@xxxxxxxxxxxxxxxxxxxxx
             Sent by:
cc
             oracle-l-bounce@f         oracle-l@xxxxxxxxxxxxx

             reelists.org
Subject
                                       Re: Primary Key seems to be
harmful
                                       for performance

             03/09/2006 10:08

             AM





             Please respond to

             breitliw@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@xxxxxxxxxxxxxxxxxxxxx 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

Other related posts: