Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Primary Key seems to be harmful for performance

Re: Primary Key seems to be harmful for performance

From: Stephen Andert <andert_at_gmail.com>
Date: Wed, 8 Mar 2006 16:48:19 -0700
Message-ID: <6d45e210603081548k3aace495y37fd1b6b99fe196c@mail.gmail.com>


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-l
Received on Wed Mar 08 2006 - 17:48:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US