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