| Same plan, same data but different performance results [message #474189] |
Fri, 03 September 2010 08:14  |
psoftneto
Messages: 44
Registered: July 2008
|
Member |
|
|
Hi all,
Can someone tell me what the principal things to look at when we have for the same query different performance results are?
I have 2 different bases: the plan and data are the same but performance results are very differents.
Thanks for your help.
|
|
|
|
|
|
|
|
| Re: Same plan, same data but different performance results [message #474195 is a reply to message #474194] |
Fri, 03 September 2010 08:33  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
The trace is what happened, the plan is what it...planned to do.
Edit: So by means of example, the plan is my intended car route. First time it took me 10 minutes, second time an hour - because the second time I got stuck in traffic.
Same route, different times, only looking at what actually happened can you begin to assess why it might have been so. Looking at my planned rouite wont answer that.
Make sense?
[Updated on: Fri, 03 September 2010 08:34] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Same plan, same data but different performance results [message #474202 is a reply to message #474200] |
Fri, 03 September 2010 08:47 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Considerations:
Is the hardware identical?
Are the loads identical?
Is the logical design (indexing etc) the same?
Are the stats both current?
Are you speed testing as a mean of concurrent runs or first time runs?
You see where I'm going yes?
Cost being the same isnt the same as the plan being the same, it might be, but it might not be. Cost is an internal metric used by oracle to chose a plan, you really shouldnt reference it in typical use.
|
|
|
|
|
|
| Re: Same plan, same data but different performance results [message #474308 is a reply to message #474219] |
Sat, 04 September 2010 22:56 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
When comparing the plans, don't just check the steps and object names. Also check the Filter Criteria and especially the Access Criteria. For example, if a concatenated index is created differently on the two databases, one plan could RANGE SCAN on just the first column and the other plan could range scan on more columns. You wouldn't know without looking at the ACCESS PREDICATES.
This, of course, would also be obvious from the trace. So just run a trace.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
