Re: Varying plans on different nodes

  • From: Steve Adams <steve.adams@xxxxxxxxxxxx>
  • To: gorbyx@xxxxxxxxx
  • Date: Fri, 08 Jun 2007 00:39:24 +1000

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@xxxxxxxxx>
To: mwf@xxxxxxxx, fairlie_r@xxxxxxxxx, Oracle-L <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxx> 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



<snip>




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


Other related posts: