Re: Primary Key seems to be harmful for performance

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: genegurevich@xxxxxxxxxxxxxxxxxxxxx
  • Date: Thu, 09 Mar 2006 09:08:15 -0700

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?


Wolfgang Breitling
Centrex Consulting Corporation


Other related posts: