Home » RDBMS Server » Performance Tuning » SQL execution plan different on different hardware (Oracle 11gR2, AIX)
SQL execution plan different on different hardware [message #569744] Wed, 31 October 2012 23:07 Go to next message
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
Re: SQL execution plan different on different hardware [message #569745 is a reply to message #569744] Wed, 31 October 2012 23:12 Go to previous messageGo to next message
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.
Re: SQL execution plan different on different hardware [message #569750 is a reply to message #569744] Thu, 01 November 2012 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: SQL execution plan different on different hardware [message #569759 is a reply to message #569744] Thu, 01 November 2012 02:36 Go to previous message
John Watson
Messages: 8922
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.
Previous Topic: NESTED LOOPS JOIN and DISTRIBUTED OPERATIONS
Next Topic: Performance degrades as dB size increases
Goto Forum:
  


Current Time: Fri Mar 29 01:53:17 CDT 2024