RE: different query performance cluster nodes
- From: "Chitale, Hemant Krishnarao" <Hemant.Chitale@xxxxxx>
- To: <eglewis71@xxxxxxxxx>
- Date: Fri, 29 Apr 2011 11:10:03 +0800
One way I use to force a hard parse is to run a dummy DDL.
Thus, if your query is a "SELECT ... FROM TABLE_A, TABLE_B ...."
a "dummy DDL" like "COMMENT ON TABLE_A IS 'this is table_a';"
(or putting the actual comment string if you already have a comment for
the table in USER_TAB_COMMENTS)
will force an invalidation of *all* SQLs aganst TABLE_A in both
instances.
Therefore, you have to be careful about selecting which table to COMMENT
on.
If TABLE_A has many other SQLs that are running well and you don't want
to force a hard parse for those, you might COMMENT ON TABLE_B.
Alternatively, you could just rewrite the SQL by changing some lower
case to Upper Case characters and test the rewritten SQL in both
instances. Since this will be a "new" SQL (with a new SQL_ID) it will
be hard-parsed.
This second option would be preferred in your case.
Hemant K Chitale
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of ed lewis
Sent: Friday, April 29, 2011 8:32 AM
To: Greg Rahn
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: different query performance cluster nodes
Greg,
I'll try you suggestion in regard to the hard parse.
I would like to try it on the node with the bad query
first. Any command you would recommend ?
I check the parameters on both servers,
and they look OK.
Thanks for your input.
----- Original Message -----
From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
To: <eglewis71@xxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, April 28, 2011 4:31 PM
Subject: Re: different query performance cluster nodes
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
This email and any attachments are confidential and may also be privileged. If
you are not the addressee, do not disclose, copy, circulate or in any other way
use or rely on the information contained in this email or any attachments. If
received in error, notify the sender immediately and delete this email and any
attachments from your system. Emails cannot be guaranteed to be secure or
error free as the message and any attachments could be intercepted, corrupted,
lost, delayed, incomplete or amended. Standard Chartered PLC and its
subsidiaries do not accept liability for damage caused by this email or any
attachments and may monitor email traffic.
Standard Chartered PLC is incorporated in England with limited liability under
company number 966425 and has its registered office at 1 Aldermanbury Square,
London, EC2V 7SB.
Standard Chartered Bank ("SCB") is incorporated in England with limited
liability by Royal Charter 1853, under reference ZC18. The Principal Office of
SCB is situated in England at 1 Aldermanbury Square, London EC2V 7SB. In the
United Kingdom, SCB is authorised and regulated by the Financial Services
Authority under FSA register number 114276.
If you are receiving this email from SCB outside the UK, please click
http://www.standardchartered.com/global/email_disclaimer.html to refer to the
information on other jurisdictions.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
