Varying plans on different nodes

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Wed, 6 Jun 2007 19:13:20 -0700 (PDT)
Message-ID: <293507.11785.qm@web31905.mail.mud.yahoo.com>

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

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

Take the Internet to Go: Yahoo!Go puts the Internet in your pocket: mail, news, photos & more.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Jun 06 2007 - 21:13:20 CDT