Re: Varying plans on different nodes

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: fairlie_r@xxxxxxxxx
  • Date: Thu, 7 Jun 2007 10:52:15 +0200

Hi fairlie

Do you have system stats in place ?

if this is 10g -- you didn't mention;  isn't it possible that upon instance
startup, system stats are gathered with noworkload per default at instance
startup. Therefore, when instance 1 starts, it gets a set of "default"
system stats, and perhaps it has them cached somewhere in memory ? And when
instance 2 starts up, it gets a different set and uses those ?

Just thinking out loud here.

Regards

Stefan



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

Thanks Greg,

But doesn't gather_system_stats populate sys.aux_stats$ and hence is
consistent throughout the cluster?

Thanks
Fairlie

*Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>* wrote:

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 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




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


------------------------------
Choose the right car based on your needs. Check out Yahoo! Autos new Car
Finder 
tool.<http://us.rd.yahoo.com/evt=48518/*http://autos.yahoo.com/carfinder/;_ylc=X3oDMTE3NWsyMDd2BF9TAzk3MTA3MDc2BHNlYwNtYWlsdGFncwRzbGsDY2FyLWZpbmRlcg--+>




--
=========================

Stefan P Knecht
Consultant
Infrastructure Managed Services

Trivadis AG
Europa-Strasse 5
CH-8152 Glattbrugg

Phone +41-44-808 70 20
Fax +41-808 70 12
Mobile +41-79-571 36 27
stefan.knecht@xxxxxxxxxxxx
http://www.trivadis.com

=========================

Other related posts: