OraFAQ Forum: Performance Tuning » SQL execution plan different on different hardware

Home » RDBMS Server » Performance Tuning » SQL execution plan different on different hardware (Oracle 11gR2, AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL execution plan different on different hardware [message #569744]

Wed, 31 October 2012 23:07

hjoshi
Messages: 7
Registered: November 2009
Location: Sydney

Junior Member

So the situation is like this

- Database A (20 tables)
- Database B (20 tables)
- Both A and B are Oracle 11gR2
- Both of these databases run on different hardware (A is a VM, B is on a physical host)
- The 20 tables in A and B have exactly same number of rows and after preparing the data, the schemas
were analysed using the same DBMS_STATS parameters

Despite this, the execution plans appear to be quite different for the same queries between A and B

I imagine there is something outside of the Oracle table rowcounts, table stats, column stats, index stats that's resulting in the different execution plans.

What "areas" have you guys focused on to diagnose this or if there is a recommended approach? I want to go about it in a scientific way but would appreciate a starting point.

Thanks

Report message to a moderator

Re: SQL execution plan different on different hardware [message #569745 is a reply to message #569744]

Wed, 31 October 2012 23:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Different results occur when something is different.
Eliminate where things are the same and what remains is where they differ.

Report message to a moderator

Re: SQL execution plan different on different hardware [message #569750 is a reply to message #569744]

Thu, 01 November 2012 01:40

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I imagine there is something outside of the Oracle table rowcounts, table stats, column stats, index stats that's resulting in the different execution plans.

You didn't say that the statistics are the same ones, just number of rows are equal but there are many other statistics.
And different OS/hardware also means different statistics.
And you didn't say that instances parameters are the same ones.

If you want the detail, activate a 10053 trace and you will know.

Regards
Michel

Report message to a moderator

Re: SQL execution plan different on different hardware [message #569759 is a reply to message #569744]

Thu, 01 November 2012 02:36

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

One point: you probably have missing or inaccurate system statistics.
select * from sys.aux_stats$;
on each to check this.

Report message to a moderator

Previous Topic:	NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS
Next Topic:	Performance degrades as dB size increases

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 12:11:21 CST 2024