This content has been marked as final. Show 4 replies
How many statements are there in the procedure, every statement in the procedure will have a different plan, Buy the way good thing will be to check the CPU time because 1 system could be having sufficient CPU resources while the other would be starving for the same. So check the same. Even the I/O cost could be an added factor. Having execution plan same doesnt mean the elapsed time would be same on different systems.
849592 wrote:on both system do as below
I have one procedure is running in one database which is taking 2 urs. But same procedure (sama data volume,same execution plan) is taking 4 hours in another database.
Both system are different and it is in window OS.
Could some please clarify what shall i check?
Thanks in advance.
ALTER SESSION SET SQL_TRACE=TRUE;
process both trace files using TKPROF & post results back here
it would be surprising if two different databases had same performance, so it is only natural that performance differs. The reason could be different I/O speed, caching efficiency, optimizer settings, etc. If you really think that you should have more similar performance, then do the following:
1) use profiler to determine which statement(s) is (are) taking most of the time
2) use rowsource statistics to understand why