I've recently been tasked with moving our Oracle 11g database from a linux CentOS desktop to a windows 2008 server. Given I've not done much behind the scenes stuff with oracle before I'm a bit out of my depth.
I did a full expdp from the source server and imported it to the target server (i built tablespaces before the load but nothing else).
Everything went well enough (a few errors that were related to having a different global_name which, from documentation, appeared ignorable.
The problem I am having is that the new server appears to take longer to run queries compared to the old server. This is in spite of more cores and more RAM. I think the problem is due to I/O limitations as the old server ran on an SSD.
I get the same 'explain plans' on queries yet getting through the blocks on v$sql_longops just seems to take longer… Can anyone provide details on what steps to take to check and compare output so I can try to ascertain the underlying issue?
Best Answer
Depending on the edition (enterprise, standard, express, etc.), the licensed options (particularly the Performance and Tuning Pack), and whether you have installed Statspack, I would start by taking an AWR or Statspack report for both system when they are executing a similar workload and compare them. If you are licensed to use the AWR (note that querying the AWR tables violates your license agreement if you aren't), use an AWR report. Otherwise, assuming you (or the prior DBA) installed Statspack, use that. Otherwise, you'll have to install Statspack in both systems.
If your guess is correct, I/O is the bottleneck, and the problem is that the I/O subsystem on the new server is slower than the I/O subsystem on the old server, you would expect that the top wait events in the report on the new server would be primarily I/O, that the amount of I/O would be consistent across the reports, and that you'd see that individual I/O operations on the various tablespaces and data files were consistently slower on the new system. Otherwise, comparing the reports should tell you what the bottleneck is (though an AWR report has a ton of information and a comprehensive discussion of interpreting an AWR report is a book not a forum post).