Re: Varying plans on different nodes

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Fri, 08 Jun 2007 00:39:24 +1000
Message-ID: <4668189C.8070506@ixora.com.au>

Hi Alex and list,

I don't know if Fairlie is online right now, but he sent me a copy of the traces and I did the diff. There were multiple points where the costs were different. There were small differences in most of the full table scan costs and about a 3:1 ratio between all the index-ffs costs. The join order costings had lots of differences too, but they appeared to be entirely due to the differences in the single table access path costs.

My guess was that workarea_size_policy = auto combined with memory pressure on node1 caused the index-ffs scan row source to scale back its memory usage and thus inflate its costs. That guess could be checked by running the 10053 traces again with workarea_size_policy = manual.

However, I fail to see where the 3:1 ratio comes from. If it is just sacrificing read-ahead buffers then a cost ratio of 2:1 would make sense although it would not be that large in practice. Maybe node3 was almost idle and index-ffs was intending to use two read-ahead buffers instead of just one? If so, the costing is even more unrealistic.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/         - For DBAs
@   http://www.christianity.net.au/  - For all

-----Original Message-----
Date: Thu, 7 Jun 2007 08:54:51 -0400
From: "Alex Gorbachev" <gorbyx_at_gmail.com> To: mwf_at_rsiz.com, fairlie_r_at_yahoo.com, Oracle-L <oracle-l_at_freelists.org> Subject: Re: Varying plans on different nodes

> Mark,
> 
> I bet it's consistent and not just the first parse as Failie was able
> to reparse it with 10053 trace.
> 
> Fairlie,
> Did you try diff on two 10053 trace? There must be something else
> that's different and difficult to catch for an eye. Also, any chance
> that node have differences in one-off patches?
> 
> Alex
> 
> 
> On 6/7/07, Mark W. Farnham <mwf_at_rsiz.com> wrote:

>> Without at all trying to be comprehensive, different nodes means
>> different
>> instances.
>>
>>
>>
>> Parameters could be different, what is in cache could be different, AWR
>> could have different accumulations of various things, and the parameter
>> values of a given sql that was parsed first on a given instance could be
>> different. Anything that the optimizer at the release level you are at
>> can
>> use that is specific to an instance rather than a database is a candidate
>> for examination.
>>
>>
>>
>> If one plan is "good enough" for all predicate values, then your
>> solution is
>> likely to be some variety of plan stabilization for that sql.
>>
>>
>>
>> My first guess is that the initial parse on the two different "nodes" had
>> wildly different predicate values so that the "bad" plan was actually
>> less
>> costly for that particular set.
>>
>>
>>
>> Regards,
>>
>>
>>
>> mwf
>>
>>
>>
>> _____
>>
>> From: oracle-l-bounce_at_freelists.org
>> [mailto:oracle-l-bounce_at_freelists.org]
>> On Behalf Of fairlie rego
>> Sent: Wednesday, June 06, 2007 10:13 PM
>> To: Oracle-L
>> Subject: Varying plans on different nodes
>>
>>
>>
>> <snip>
>>
>>

>
>

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

Received on Thu Jun 07 2007 - 09:39:24 CDT