0

I'm looking for ideas regarding measuring the bandwidth of an existing Oracle DB connection (perl DBI) without any changes to database server side. Right now I'm running a

select * from table;

against a table with approximately known amount of data and timing the response. I'm running it manually from shell and I'm considering implementing similar functionality in the application's admin/debug section for admins to look at. Specifically, I'm looking at running prepare first and then running execute while timing performance using Time::HiRes.

Questions:

  1. Is there a better SQL statement to use for the benchmark? Perhaps some query could generate a specific amount of non-meaningful data on the fly, much like dd if=/dev/zero bs=1k count=1k
  2. Can someone think of another approach to measuring bandwidth that might be integratable to the web UI? A non-interactive shell command would work fine.

A little background. My application is accessing Oracle DB over an internal network. The network has bandwidth problems. On a bad day it's as bad as a dial-up. Implementation environment is RHEL / Oracle Instantclient / Perl DBI.

Mikko Lipasti
  • 171
  • 5
  • 1
    Welcome to StackOverflow. Please refrain from asking questions that recommend an approach or tool. The forum is meant for specific coding obstacles. There are other Forums on Stack Exchange that may better suit your needs. – Renaissance Oct 15 '13 at 07:21
  • 2
    `man iperf`; your network guys should be familiar with it – mpapec Oct 15 '13 at 08:32
  • Apologies for sloppy writing on the first try. I rewrote my question - is this better now? Not sure I fully understood all remarks though. – Mikko Lipasti Oct 16 '13 at 05:46
  • @mpapec Thanks, looks like a very robust tool. That looks like perfect plan B for me - right now I'm looking into possibilities for measuring bandwidth with zero additional tooling on the database server side. Additionally a bunch of firewalls along the way make life a bit more difficult at times. – Mikko Lipasti Oct 16 '13 at 05:49
  • @MikkoLipasti yes, but bandwidth analysis should be IT/network responsibility. They have access to switches and corresponding `SNMP` bandwidth data (http://www.mikrotik.com/thedude to give them a clue). – mpapec Oct 16 '13 at 06:22
  • @mpapec agreed; however sometimes you need metrics of your own to independently verify that their solution in fact is fixing the problem. – Mikko Lipasti Oct 16 '13 at 08:42

1 Answers1

1

You could try running a little script on the Oracle server that sits in a loop, reading from a specified port (using netcat) and discarding the data to /dev/null. Then have a script on your client that sends a known volume of data every now and then (using netcat) and times how long it takes. That pretty much measures network performance and is independent of Oracle or disk.

Something like this:

On Oracle Server

while /bin/true
do
    nc -l 20000 > /dev/null
done

On client

time dd if=/dev/zero bs=1024k count=10 | nc <oracle_ip> 20000
Mark Setchell
  • 191,897
  • 31
  • 273
  • 432
  • Thank you for the suggestion. My bad - I neglected to mention in the first version that I'd rather not rely on putting anything extra on the database server side since it's not in my control. I'm primarily looking at ways to utilize the existing connection to also measure the bandwidth with. Excellent Plan C though, in case iperf isn't available. – Mikko Lipasti Oct 16 '13 at 05:51