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-%20the% 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