Re: Primary Key seems to be harmful for performance
- From: "Terry Sutton" <terrysutton@xxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 15 Mar 2006 08:06:15 -0800
Igor,
Yeah, I saw that message about "looking at the wrong file", but too late. I
missed the part where he said he couldn't add hints. I wondered about that,
but figured if he can change the query to say
"CALL_CNTR_EMPL_MTHLY_HIST.EMPL_KEY+0" then he can change the query to add a
hint.
Oh well, if he comes back with more info, maybe we can have some fun. :-)
--Terry
----- Original Message -----
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Terry Sutton
Sent: Tuesday, March 14, 2006 10:48 PM
To: genegurevich@xxxxxxxxxxxxxxxxxxxxx; 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@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
>
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
