Re: Varying plans on different nodes

  • From: fairlie rego <fairlie_r@xxxxxxxxx>
  • To: Steve Adams <steve.adams@xxxxxxxxxxxx>, gorbyx@xxxxxxxxx, RS2273@xxxxxxx
  • Date: Thu, 7 Jun 2007 10:22:41 -0700 (PDT)

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@xxxxxxxxxxxx> 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@xxxxxxxx, fairlie_r@xxxxxxxxx, 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@xxxxxxxxxxxxx 
>> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
>> 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. 

Other related posts: