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?

  • 1
    This is an absolutely massive question. The answer could, quite literally, be anything. If could be related to the disks, yes, or any one of your Oracle parameters, or the OS, or the new machine. It's unlikely that anyone will be able to give you good advice, even if you posted everything possible (you haven't posted anything relevant). There's a reason why DBAs and sysadmins get paid good money. Chances are you need to get both to look at your problem.
    – Ben
    Oct 26 '12 at 7:52
  • 2
    As a little aside why are you assuming that you're having I/O problems. I recently upgraded a box; quadrupled the RAM and the number of cores and moved from SCSIs to SSDs. The box is CPU bound, which is definitely not what we were expecting as all work done on it is I/O.
    – Ben
    Oct 26 '12 at 7:53

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).

  • Thankyou very much for your help. This has sent me down the right path. I wasn't hoping for "How to make oracle perfect in 3 easy steps" I just wanted some practical avenues for investigation and some tips on what to look out for.
    – Ewanw
    Oct 31 '12 at 23:15

Tuning an instance is a complex problem, which requires experience and a deep understanding of how databases work. People make very fine livings from troubleshooting poorly-performing systems. They couldn't do that if it was possible for absolute novices to diagnose things with some advice from random strangers on the internet and a couple of URLs.

However, I wouldn't worry. Your organisation has passed this difficult and demanding task to somebody with no experience in Oracle administration, so presumably they don't think it's that important. Please understand, I am no belittling you, it's not your fault you have been placed in this situation.

But you mustn't ignore it. Your best strategy would be to talk to your boss, admit you're out of your depth and tell them they need to hire a DBA, or at least get a consultant in for a few days.

  • 1
    You might not be belittling but you aren't exactly constructive or helpful either. Sure, databases are complex beasts but they aren't rocket science. To not be able to say "Use the O/S Resource monitors" or "explain plan on the heavy queries and determine how they could be improved using X,Y,Z as a guide" shows a level of conceit, self-importance or perhaps just plain unhelpfulness. Even saying "try the database import a different way" might have helped, this thing doesn't need to be tuned to the nth degree, I was just hoping to get a few practical tips or starting points...
    – Ewanw
    Oct 26 '12 at 11:11
  • 1
    If you wanted generic advice, I included links to some helpful parts of the documentation. I'm sorry you felt my specific advice was not useful but please let me assure you, I offered it sincerely. I have been in similar spots myself, In every case an honest engagement with the management team has been part of my strategy. Openness gives you a chance to set your boss's expectations and buys you time to get your shizzle together.
    – APC
    Oct 26 '12 at 20:09

SSD is like a Ferrari and traditional hardisks are like a Toyota corolla. You will never have the performance of SSD no matter what parameter you tweak or having more cpu's and more sga in your new server.

  • What is SGA? I've never heard of that...
    – Hannah Vernon
    Nov 1 '12 at 4:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.