Home » RDBMS Server » Performance Tuning » Different execution plan in Different DB (Oracle 10g)
Different execution plan in Different DB [message #590613] Sat, 20 July 2013 06:18 Go to next message
sss111ind
Messages: 473
Registered: April 2012
Location: India
Senior Member

Hi All,

Why the query is behaving differently with the different database.(execution plan)

Whatever the production database is having same database instance replicated to a new schema.
I tried both the queries running on both environment.In prod the index has been used but in newdev it is not.
This case existing primary key index were not been used.
What might be the issue.

Regards,
Nathan

[Updated on: Sat, 20 July 2013 06:20]

Report message to a moderator

Re: Different execution plan in Different DB [message #590618 is a reply to message #590613] Sat, 20 July 2013 07:57 Go to previous messageGo to next message
Michel Cadot
Messages: 58644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not the same data
Not in the same place
Not the same storage
Not the same cpu
Not the same workload

Regards
Michel
Re: Different execution plan in Different DB [message #590619 is a reply to message #590618] Sat, 20 July 2013 09:05 Go to previous messageGo to next message
sss111ind
Messages: 473
Registered: April 2012
Location: India
Senior Member


HI Michel,

Yes the datas are same.Because all prod datas are moved and I started comparing.
Still the query execution time is different.
And What are factors need to be take care of and How to check what affecting the query.

Regards,
Nathan
Re: Different execution plan in Different DB [message #590620 is a reply to message #590619] Sat, 20 July 2013 09:12 Go to previous messageGo to next message
BlackSwan
Messages: 22558
Registered: January 2009
Senior Member
post (formatted) EXPLAIN PLANs from both instances
Re: Different execution plan in Different DB [message #590621 is a reply to message #590619] Sat, 20 July 2013 09:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
All other ones.

Regards
Michel
Re: Different execution plan in Different DB [message #590622 is a reply to message #590621] Sat, 20 July 2013 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 22558
Registered: January 2009
Senior Member
different results occur when something is different.
Only you can compare & contrast the two environments; since they are unique to your locality.
Re: Different execution plan in Different DB [message #590655 is a reply to message #590613] Sun, 21 July 2013 05:22 Go to previous message
Lalit Kumar B
Messages: 1885
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Sat, 20 July 2013 16:48
In prod the index has been used but in newdev it is not.


1. The stats might be different between the two environments, hence the optimizer behaves differently. The optimizer can only use the information that is provided to it. If the stats it uses are incorrect, old, or skewed then the optimizer stays unaware about it, it is more likely to choose an inefficient plan for fetching the data.
2. Even if the data is same, there are lot of other things on which the performance depends, like the initialization parameters etc. Since you say it is "newdev", I am sure it won't be same as that of production.

[Updated on: Sun, 21 July 2013 05:28]

Report message to a moderator

Previous Topic: About NO_INDEX Hint
Next Topic: Exec plan explanation
Goto Forum:
  


Current Time: Sat Aug 02 00:28:48 CDT 2014

Total time taken to generate the page: 0.10602 seconds