RE: different query performance cluster nodes
- From: "Jorgensen, Finn" <Finn.Jorgensen@xxxxxxxxxxxxxxxxx>
- To: "'eglewis71@xxxxxxxxx'" <eglewis71@xxxxxxxxx>, 'Job Miller' <jobmiller@xxxxxxxxx>, "'greg@xxxxxxxxxxxxxxxxxx'" <greg@xxxxxxxxxxxxxxxxxx>
- Date: Fri, 29 Apr 2011 11:41:17 -0400
With cursor_sharing='SIMILAR' all those hardcoded "acme" will become system
bind variables. What does the query in the shared pool look like?
What is the output of this query when you plug in your owner and table name?
select column_name , num_distinct, density , num_nulls , last_analyzed,
histogram from dba_tab_col_statistics i
where i.owner = upper('&owner')
and i.table_name = upper('&tab_name')
Thanks,
Finn
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of ed lewis
Sent: Friday, April 29, 2011 9:09 AM
To: Job Miller; greg@xxxxxxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: different query performance cluster nodes
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@xxxxxxxxx>
To: <eglewis71@xxxxxxxxx>; <greg@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxx> wrote:
> From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
> Subject: Re: different query performance cluster nodes
> To: eglewis71@xxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxxxxxx>
> 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@xxxxxxxxx>
> 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
>>> This e-mail and any attachments are confidential, may contain legal,
professional or other privileged information, and are intended solely for the
addressee. If you are not the intended recipient, do not use the information
in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP2
--
//www.freelists.org/webpage/oracle-l
Other related posts:
