VMware Cloud Community
oldschooler
Contributor
Contributor

oracle database consolidation effort - physical to VMware

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?

Reply
0 Kudos
2 Replies
Simon_H
Enthusiast
Enthusiast

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.

HTH,

Simon

Reply
0 Kudos
oldschooler
Contributor
Contributor

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.

Reply
0 Kudos