RE: different query performance cluster nodes

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ed lewis Sent: Friday, April 29, 2011 8:32 AM
To: Greg Rahn
Cc: oracle-l_at_freelists.org
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_at_structureddata.org> To: <eglewis71_at_gmail.com> Cc: <oracle-l_at_freelists.org> 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_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


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.

--
http://www.freelists.org/webpage/oracle-l