RE: Primary Key seems to be harmful for performance

Though you don't provide execution plans for any fast/slow (with/out PK) executions, it looks like "slow" plan is using index, while "fast" plan doesn't (probably using full table scan for this specific table).

You are saying, you have all the statistics collected for the tables involved (PK index in question included?). Which means, that probably PK index statistics (clustering factor?) are misleading for optimizer, see new book Jonathan Lewis "Cost-Based Oracle Fundamentals" (you can find Chapter 5 on-line, and it specifically talks about "clustering factor"). Jonathan Lewis also shows how to "correct" clustering factor calculated by Oracle but not being "truly representative of the way the data really clustered in the table".

You don't specify your Oracle version.
So, I'd suggest in order to help optimizer to choose "correct" (fast) plan, either play with optimizer_index_caching or optimizer_index_cost_adj init parameters (check if they were modified from default values) or set them to default and collect system statistics, dending on your Oracle version.

Igor  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discoverfinancial.com
Sent: Thursday, March 09, 2006 9:12 AM
To: oracle-l_at_freelists.org
Subject: Re: Primary Key seems to be harmful for performance

I was refering to the fact that i had multiple tables in my query and they all have PKs. I should have been clearer though and say that I have disabled a PK on one of the tables. I can't modify the SQL in any way because it is generated by a reporting tool (Cognos) it won't add hints or +0 etc.

What I was mostly wondering is whether this situation (PK createing a performance degradation) is a symptom of some other problem which I need to look at. My first suspect was statistics, but I ruled that out by analyzing all tables involved.

thank you

Gene Gurevich
Oracle Engineering
224-405-4079  

             Nuno Souto

             <dbvision_at_iinet.n

             et.au>
To

             Sent by:                  oracle-l_at_freelists.org

             oracle-l-bounce_at_f
cc 
             reelists.org

 
Subject 
                                       Re: Primary Key seems to be
harmful 
             03/08/2006 06:39          for performance

             PM

 

 

             Please respond to

             dbvision_at_iinet.ne

                   t.au

 

 

Quoting genegurevich_at_discoverfinancial.com:

> in over 2 hours. After looking at the results of the tkprof, I decided

> to disable one of the primary keys. After that the

"one of the primary keys"? That would be of one of the tables?

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

Without any specific data on execution plans, all I can reasonably suggest is that you use a function in the query to disable use of the pk index by the optimizer. Something like the traditional use of a nvl() around the leading column(s) of the index, or concatenating a null to a string or adding 0 (zero) to a number column.

All of them classical ways of making the optimizer ignore a particular index. You could of course use a hint as well. That'd be a safer propposition than dropping a PK and losing uniqueness checks?

--
Cheers
Nuno Souto
from sunny Sydney
--
http://www.freelists.org/webpage/oracle-l






--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l