Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Primary Key seems to be harmful for performance
When was the last time the PK has been rebuilt and how much data has been
added/deleted/changed since then?
Do you have statistics on the index and how recently gathered (in comparison to rate of change)?
Stephen
On 3/8/06, genegurevich_at_discoverfinancial.com <
genegurevich_at_discoverfinancial.com> wrote:
>
> Everybody:
>
> 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?
>
> thank you in advance
>
> Gene Gurevich
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- Stephen Andert http://spaces.msn.com/andert-news/ -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 08 2006 - 17:48:19 CST
![]() |
![]() |