Re: Primary Key seems to be harmful for performance
- From: genegurevich@xxxxxxxxxxxxxxxxxxxxx
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 10 Mar 2006 17:03:45 -0600
Mladen:
Thanks for your help. I have posted the SQL and the explain plans a day or
two ago. My optimizer parameteres
are set to optimizer_index_cost_adj 100 and optimizer_index_caching to 0 (
I think these are default parms). I'll
need to read up on tuning them. I have tried using ALL_ROWS hint but that
did not change the performance.
Can't throw out Cognos, I'm afraid :(
thank you
Gene Gurevich
Oracle Engineering
Mladen Gogala
<gogala@sbcglobal
.net> To
Sent by: oracle-l <oracle-l@xxxxxxxxxxxxx>
oracle-l-bounce@f cc
reelists.org
Subject
Re: Primary Key seems to be harmful
03/09/2006 06:40 for performance
PM
Please respond to
gogala@sbcglobal.
net
On 03/09/2006 09:12:05 AM, genegurevich@xxxxxxxxxxxxxxxxxxxxx wrote:
> 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.
Gene, execution plan would be helpful. What you wrote sounds like a full
table scan,
murdered by using index. That usually happens if parameters with the goal
of forcing
index use and emulating RBO are in effect (OPTIMIZER_INDEX_CACHING and cost
adjust).
What happens is that Oracle reads every index key and then reads the
corresponding
table block, performing, in effect, several times as many I/O requests as
without
using an index. Two other things you can do are to make sure that users
that use
Cognos have optimizer goal set to ALL_ROWS and you can fake statistics and
make the
PK index look bad to CBO (look at my home page, I describe the way of doing
that
there). Third option is to try throwing Cognos out and then getting
something
that always generates an optimal execution plan, like Business Objects or
Crystal
Reports (duck).
--
Mladen Gogala
http://www.mgogala.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
