You are right. I have either looked at a wrong file or did not compare the files correctly. I'll need to look again at this query thank you Gene Gurevich Oracle Engineering "Igor Neyman" <ineyman@perceptr on.com> To Sent by: <genegurevich@xxxxxxxxxxxxxxxxxxxxx oracle-l-bounce@f >, <oracle-l@xxxxxxxxxxxxx> reelists.org cc Subject 03/13/2006 08:09 RE: Primary Key seems to be harmful AM for performance Please respond to ineyman@perceptro n.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@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 -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l