RE: Primary Key seems to be harmful for performance

Terry,

Thanks for clearer explanation of what I was trying to tell Gene (yes, it's his query).

But, according to Gene's original message he can't modify this query (i.e. add hints) - it's generated by Cognos. So, he was asking what could be done on the "database side".

Besides, according to his latest message, it's possible that he published wrong plans (either for "fast" or "slow" query), so all this discussion is moot.

Igor

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Terry Sutton Sent: Tuesday, March 14, 2006 10:48 PM
To: genegurevich_at_discoverfinancial.com; oracle-l Subject: Re: Primary Key seems to be harmful for performance

Igor,

What the "CALL_CNTR_EMPL_MTHLY_HIST.EMPL_KEY+0" does is just change what gets looked at when when the optimizer does its analysis (if you really want to see how, do a 10053 trace on both versions of the query. Wolfgang has an excellent explanation of how to do this at http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20 the%
2010053%20Event.pdf). It's not whether it's using that PK or not, it's just that the steps done by the optimizer are done in a different order.

Looking at your execution plans, the main difference is when the full scan of CALL_CNTR_EMPL_MTHLY_HIST occurs. Since that is part of an inline view, and in the fast plan the inline view with MTH_TM_PERD in it is accessed first, you might try putting a nomerge hint into the inline view with MTH_TM_PERD. I'm not positive that's the best way to get the path you want, but something along those lines would probably help. You want the execution path to access CALL_CNTR_EMPL_PK and MTH_TM_PERD before CALL_CNTR_EMPL_MTHLY_HIST, as that seems to eliminate the most rows quickly based on the execution plans you've posted.

--Terry

> 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_at_sbcglobal
> .net>

To
> Sent by: oracle-l
<oracle-l_at_freelists.org>
> oracle-l-bounce_at_f

cc
> reelists.org
>

Subject
> Re: Primary Key seems to be
harmful
> 03/09/2006 06:40 for performance
> PM
>
>
> Please respond to
> gogala_at_sbcglobal.
> net
>
>
>
>
>
>
>
> On 03/09/2006 09:12:05 AM, genegurevich_at_discoverfinancial.com 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

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



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