Re: different query performance cluster nodes

From: Thiago Maciel <thiagomaciel_at_gmail.com>
Date: Fri, 29 Apr 2011 11:09:10 -0300
Message-ID: <BANLkTim2AVKJkkknC-8buanUsKvzcVdzqg_at_mail.gmail.com>

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/

<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.
>
>
> http://blogs.oracle.com/optimizer/2010/03/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force.html
>
>
> --- 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-l

Received on Fri Apr 29 2011 - 09:09:10 CDT