Hate to post sort of a generic question but just trying to figure out what I might look for to try and narrow down the root cause of this issue.
Old environment is RH4 64-bit Linux servers running primarily oracle 10g EE.
Due to licensing costs etc. moving to a virtualized environment (VMware vSphere) with VM's running RH5 64-bit.
Couple of db moves seemed to go ok but the etl jobs that run to Dev Data Warehouse take much longer to complete like 60 minutes to now 100 minutes. So it seems to be related to queries over database links so far but more testing to be done with other apps as we migrate.
I know lots of variables involved here so maybe hard to make any suggestions but to summarize the move is from older Intel servers to much newer and faster servers with huge memory footprints and newer faster SAN array.
- tried increasing the ram and vcpu's made available to the VM and in turn bumped up sga_max_size and shared_pool_size
- increased redo log sizes but so far no noticable improvement
By monitoring the sql job on both the source and destination databases the connection details shows a high # for the Time Waited column in the "SQL*Net message" entry.
- tried adding TCP.NODELAY = YES to the sqlnet.ora file
- no change
- is this an indication of a network latency or setting issue or a red herring?
Via an Oracle article is says to enable HugePages on Linux [ID 361468.1]
- done but not exactly sure how to substantiate the change other than I can see that "grep Huge /proc/meminfo" now shows some values.
Maybe it is just poor sql?
Have you tried comparing the execution plan for the specific SQL when it runs on the old and new systems? That should be the first step. If they are different you can start to investigate why. If they are identical then you can start to look at some of these more generic system-wide changes, but given the amount of change (processors, SGA, storage, probably database release, etc) I suspect it is something much simpler and not related to virtualisation.
As usual there is no point in changing something if there is no performance diagnostics evidence to support it.
Thanks for the reply Simon. The issue with trying to compare execution plans is it is a package with a bunch of procedures in it doing table truncates followed by lots of inserts via selects from the destination tables. I guess we could pull out a section of that package and try and run just one query for comparison.
The plan now is to revert back to the original config and yes there was a db upgrade and OS upgrade in there as well so we will try and revert back to post move and progress forward while monitoring the effect of the changes.