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 differentinstances. 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 bedifferent. Anything that the optimizer at the release level you are at canuse 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 islikely to be some variety of plan stabilization for that sql. My first guess is that the initial parse on the two different "nodes" hadwildly different predicate values so that the "bad" plan was actually lesscostly 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>
-- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l
