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_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