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
autotrace traceonly statistics and
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.
- 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.
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.