Re: Varying plans on different nodes

The object stats would be the same, but it is possible for the system
stats to be different.  Have you validated they are consistent across
nodes?  Perhaps it would be best to snag the system stats that produce
the good plan and import them to the other nodes.

On 6/6/07, fairlie rego <fairlie_r@xxxxxxxxx> wrote:

Hi all,

I have a query which has a good plan on one node (node 3) and a bad plan
(node 1)  caused by a Merge Join Cartesian on another.

Bind variable peeking is disabled and dynamic sampling is disabled for this
query. There is no difference in v$sql_optimizer_env for the query across
the 2 nodes.

In the 10053 output I see a difference in RSC_IO for fast full scan of
indexes which I thought was based on the leaf blocks and blevel. Given that
the stats across nodes are the same how can this happen?.  If this is due to
varying load because of workarea_size_policy how do we protect ourselves.

I can send the 10053 outputs but here is one snippet

Node 3
======
  Access Path: index (index-ffs)
    Index: ADVANCED_FILTER_PK
    rsc_cpu: 875652262   rsc_io: 8606
    ix_sel:  0.0000e+00    ix_sel_with_filters:  1.0000e+00

Node 1
======
  Access Path: index (index-ffs)
    Index: ADVANCED_FILTER_PK
    rsc_cpu: 875652262   rsc_io: 26521
    ix_sel:  0.0000e+00    ix_sel_with_filters:  1.0000e+00


Thanks
Fairlie



--
Regards,

Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

Other related posts: