Re: Primary Key seems to be harmful for performance

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


Other related posts: