Re: Varying plans on different nodes

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_at_yahoo.com> 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_at_structureddata.org>* 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_at_trivadis.com
http://www.trivadis.com

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

--
http://www.freelists.org/webpage/oracle-l