Oracle: terrible execution times from virtual application server

Question

I've been provided with an Oracle VM linux box playing host to an Oracle 11g installation. I've also been provided with an Oracle VM Windows Server 2008 64-bit box acting as an application server. Tasked with deploying my application to this environment, I've created / populated the DB, installed instantclient drivers and SQL*Plus on the application server and ran a few simple queries...

...and performance is terrible. A selection on 9000 rows takes 30 seconds.

N.B. the same SELECT * FROM ... ; query is being used as a baseline, executed from SQL*Plus with timing on, autotrace traceonly statistics and arraysize 5000.

Things I've tried:

• HRPING the DB server from the application server - response time is around 0.5ms (slow, but not this slow)
• Execute the query using SQL*Plus directly on the DB server - execution time is 0.2 seconds
• Execute the query using the exact same instantclient drivers / SQL*Plus client from a different (physical) machine on the network - execution time is 0.2 seconds
• Checked that tablespaces are not full
• Checked that tmpfs (/dev/shm) space is not full
• Check that no AV / firewalls are running

I'm convinced this is an infrastructure / networking issue, but the infrastructure admin insists it is a configuration / software issue.

Other factors:

• The application is using manual IPv4 settings, and the DNS server was not specified... The local machine I used for testing had Automatic Settings. I've now requested for the correct details to be applied. Could this cause the issue?
• There's an undiagnosed (unsolved) issue whereby if either the application server or the DB is restarted, it drops off the network and requires a shutdown/startup command to be issued via the Oracle VM client to bring it back
• The application server came with Oracle PV drivers installed... I uninstalled to see if this would have any impact, but no difference has been observed

I'm all out of ideas. Any suggestions? Thanks in advance.

EDIT:

I'm currently looking into DNS settings. They were missing on the application server, and I'm not sure that they're correct on the DB server in /etc/resolv.conf either. I found a couple of resources suggesting that incorrect DNS settings could cause very poor performance.

Answer 1

This is almost certainly a networking issue (as the execution on a different machine is way faster) so I doubt you'll be able to go much further with any Oracle tools. I've never had any needs to tweak out-of-the-box network parameters to get good performance.

What I suggest is you use a standard network benchmarking tool, e.g. iPerf. It needs to be cross-platform since you have a mixed Linux/Windows environment. Keep in mind that the issue could be due to either low bandwidth or high latency. Run the same test from the other client from which you're getting good SQL Plus results, compare and if it's massively different send the evidence to your networking admin guys.

By the way, if you still need more advice I suggest you post to https://serverfault.com/ as you'll likely to reach more experienced network admins there.

Answer 2

By using Oracle's tracing of wait events you can prove the exact cause of the slowdown. This works for any query, so if you have a performance problem in the i/o subsystem, or network problems, then this will reveal it.

Have a look at the docs for your version, as the interface to the trace process has changed historically to make it rather more "friendly" (well, relatively).

http://docs.oracle.com/cd/B19306_01/server.102/b14211/sqltrace.htm#i4763

You might also try a tnsping from the client -- it's an Oracle tool that pings the database server and gives you more relevant round-trip times.

Answer 3

Oracle query is compiled and processed is some "environment". In rare cases some differences in an "environment" can lead to situations where the same query has different execution plan when executed from sqlplus, PL/SQL versus JDBC. Just to be 100% sure, try to check v$sql_plan and compare the real execution plan of the query when beeing executed from various clients.