Re: different query performance cluster nodes
Date: Thu, 28 Apr 2011 21:22:50 -0700
Message-ID: <BANLkTikfzF7Froqbtt1p-M1FbDBgsWkGvQ_at_mail.gmail.com>
The other scenario I have seen that causes different plans on RAC nodes is when a query uses bind variables and the "left side" column has a histogram on it (e.g. foo = :b1). If the bind happens to be a popular value perhaps it results in a table scan but if it is a non-popular value it could result in index access. Subsequent execution of that cursor wont change the plan, even if the bind value normally would result in such. This is why it's a bad idea to mix binds and histograms...
On Thu, Apr 28, 2011 at 1:31 PM, Greg Rahn <greg_at_structureddata.org> wrote:
> System stats only exist for a database, not an instance so that
> shouldn't be the issue.
>
> What if you cause a hard parse on both nodes - what plans do you get -
> the same ones?
>
> What may be the issue is that stats changed and there was an existing
> cursor and it has not been invalidated which could explain different
> plans on different nodes. The other thing to check is that there are
> no differences in parameters across instances.
>
> On Thu, Apr 28, 2011 at 11:39 AM, Edward Lewis <eglewis71_at_gmail.com> wrote:
>> We have a 2 node rac cluster.
>> When I run a query on node 1, it executes in 1-2 seconds.
>> When I run the same query on the node 2, it takes over
>> 2 minutes.
>> The query plans are different with node 1 using an index,
>> and node 2 doing a full table scan.
>> The first server has around 583K consistent gets, and
>> 6400 physical reads. The second server has 284 consistent gets, and
>> 88 physical reads.
>> Don't know if this is an issue, but I run system statistics,
>> but only on 1 node. I haven't been able to find anything on running
>> system stats in a cluster. Is it necessary to run system stats
>> on both servers ?
-- Regards, Greg Rahn http://structureddata.org -- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 28 2011 - 23:22:50 CDT