Varying plans on different nodes
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 6 Jun 2007 19:13:20 -0700 (PDT)
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.
Other related posts:
