Re: Varying plans on different nodes

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Thu, 7 Jun 2007 10:22:41 -0700 (PDT)
Message-ID: <452042.85262.qm@web31908.mail.mud.yahoo.com>

Thanks for your emails

Its 3:00 am in windy and rainy Sydney and not sure why Fairlie is actually awake.

The plans are indeed same with workarea_size_policy MANUAL (i.e no MJC)

Thanks
Fairlie

Steve Adams <steve.adams_at_ixora.com.au> wrote: 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"
To: mwf_at_rsiz.com, fairlie_r_at_yahoo.com, Oracle-L 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 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
>>
>>
>>
>>
>>
>>

>
>

          Fairlie Rego
Senior Oracle Consultant
  http://el-caro.blogspot.com/
  M: +61 402 792 405

Get the free Yahoo! toolbar and rest assured with the added security of spyware protection.

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

Received on Thu Jun 07 2007 - 12:22:41 CDT