Re: different query performance cluster nodes
Date: Tue, 3 May 2011 08:29:32 -0400
Message-ID: <84E6B4A44BF9455D80D1EDAA9400D397_at_CATHY>
Hi,
I followed the recommendation offered by Thiago. I flushed the problem sql statement from shared pool on instance #2. I then ran the query again on instance #2. It ran as expected in 1 second, same run time as instance #1. Looks good.
Thanks Thiago, and everyone else for their time, and their input.
ed
- Original Message ----- From: Thiago Maciel To: eglewis71_at_gmail.com Cc: Job Miller ; greg_at_structureddata.org ; oracle-l_at_freelists.org Sent: Friday, April 29, 2011 10:09 AM Subject: Re: different query performance cluster nodes
Lewis, is the issue still happening? If so, try to flush the single sql statement using the feature described below:
http://kerryosborne.oracle-guy.com/2008/09/flush-a-single-sql-statement/
See that on 10g you have to set an event...
After that, if the problem continues, you could try enable an 10053 trace and see what´s happening inside Oracle.
Regards,
On Fri, Apr 29, 2011 at 10:08 AM, ed lewis <eglewis71_at_gmail.com> wrote:
Thanks Job, and Greg,
We do have cursor_sharing set to "similar", as a requirement of the vendor.
There are no histograms on this table, outside of the default 1 histogram per column.
Here is the query.
SELECT * FROM
( SELECT T2016.C1,C3
FROM aradmin.T2016
WHERE ((T2016.C7 = 3000) AND (('Acme' = '')
OR ('Acme' = ' ') OR ('Acme' = T2016.C1000003299))
AND ((T2016.C2 = 'acme1') OR (T2016.C4 = 'acme1')
OR (T2016.C112 LIKE '%;''acme1'';%')
OR (T2016.C112 LIKE '%;0;%') OR (T2016.C112 LIKE '%;-20000;%')
OR (T2016.C112 LIKE '%;-20016;%') OR (T2016.C112 LIKE '%;804;%')
OR (T2016.C112 LIKE '%;803;%') OR (T2016.C112 LIKE '%;20313;%')
OR (T2016.C112 LIKE '%;20316;%') OR (T2016.C112 LIKE '%;13006;%')
OR (T2016.C112 LIKE '%;440;%') OR (T2016.C112 LIKE '%;20315;%')
OR (T2016.C112 LIKE '%;20055;%') OR (T2016.C112 LIKE '%;20211;%')
OR (T2016.C112 LIKE '%;802;%') OR (T2016.C112 LIKE '%;1005000400;%')
OR (T2016.C112 LIKE '%;1005000754;%') OR (T2016.C112 LIKE '%;1005000815;%')
OR (T2016.C112 LIKE '%;1005000930;%') OR (T2016.C112 LIKE '%;20032;%')
OR (T2016.C112 LIKE '%;20216;%') OR (T2016.C112 LIKE '%;20028;%')
OR (T2016.C112 LIKE '%;20029;%') OR (T2016.C112 LIKE '%;20023;%')
OR (T2016.C112 LIKE '%;20024;%') OR (T2016.C112 LIKE '%;20025;%')
OR (T2016.C112 LIKE '%;20000;%') OR (T2016.C112 LIKE '%;20004;%')
OR (T2016.C112 LIKE '%;7110;%') OR (T2016.C112 LIKE '%;20007;%')
OR (T2016.C112 LIKE '%;20002;%') OR (T2016.C112 LIKE '%;20218;%')
OR (T2016.C112 LIKE '%;20213;%') OR (T2016.C112 LIKE '%;1000000440;%')
OR (T2016.C112 LIKE '%;1005001034;%')))
ORDER BY 2 DESC, 1 ASC ) WHERE ROWNUM <= 2
- Original Message ----- From: "Job Miller" <jobmiller_at_yahoo.com> To: <eglewis71_at_gmail.com>; <greg_at_structureddata.org>
Cc: <oracle-l_at_freelists.org>
Sent: Friday, April 29, 2011 7:20 AM
Subject: Re: different query performance cluster nodes
I would bet that it is most likely what Greg indicates below. (binds + histograms with each node processing a different bind with different selectivity on startup)
You didn't ever show us the query, but if it has bind variables and you have histograms on any of the columns in the predicates, than this is almost surely the problem.
lots of folks have blogged/written about this in the past.
Adaptive cursor sharing (ACS) [in 11g] is supposed to address that issue.
- On Fri, 4/29/11, Greg Rahn <greg_at_structureddata.org> wrote:
From: Greg Rahn <greg_at_structureddata.org>
Subject: Re: different query performance cluster nodes
To: eglewis71_at_gmail.com
Cc: oracle-l_at_freelists.org
Date: Friday, April 29, 2011, 12:22 AM
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-l
--
http://www.freelists.org/webpage/oracle-l
-- http://www.freelists.org/webpage/oracle-lReceived on Tue May 03 2011 - 07:29:32 CDT