Re: Primary Key seems to be harmful for performance
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 9 Mar 2006 14:02:31 -0600
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
