-
Notifications
You must be signed in to change notification settings - Fork 18
Measuring Oracle Net Latency
The Oracle Net latency is often an important factor for applications that are "chatty", which typically means they are executing many, simple SQL statements such as simple queries or DML with just few rows. The tnsping tool that is available in most Oracle installations provides information about the availability of the tns listener, but the actual values reported by it are not representative of the actual time to do an Oracle Net roundtrip between an application server and a database. As part of the RWP*Load Simulator, there are two tools that properly measure the latency - or roundtrip time - between an application server and a database. In addition to being part of the complete RWP*Load Simulator, these tools are also available completely stand-alone.
The ociping tools makes one log on to a database , and then runs OCIPing() once per second for some period, showing the time for each roundtrip. The output is somewhat similar to the standard Linux 'ping' utility. By default, the tool runs for 60 seconds, and at completion, the average and standard deviation is shown. This tool is good in "stable" environments with little latency variation and has the benefit of showing lots of OCIPing on a single database connection.
A sample call using a URL style connect string and the resulting output is:
ociping -l username/{password}@//host/service --period=10
RWP*OCIPing Release 3.1.2.0 Production on Wed, 08 May 2024 12:57:32 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
0.322 0.0
0.286 1.0
0.307 2.0
0.313 3.0
0.331 4.0
0.284 5.0
0.294 6.0
0.292 7.0
0.318 8.0
0.314 9.0
ociping (ms) mean=0.306, stddev=0.015, min=0.284, max=0.331
You can do the same if you have the full installation of rwloadsim.
The connping tool has a few differences to the ociping tool, most importantly that it does not only establish one database connection initially, in stead it will establish a new database connection every second and subsequently use that database connection to perform the OCIPing() call. Additionally, it will also execute a very simple 'select 1 from dual' query. The time to do all three: Logon to the database, do OCIPing() and do the query is output. By default, connping runs for 60 seconds and it shows the averages and standard deviation of all three at the end.
A sample call in this case using an alias from tnsnames.ora and the resulting output is:
connping -l username/{password}@tnsalias --period=10
RWP*Connect/OCIPing Release 3.0.2.2 Production on Thu, 11 Aug 2022 09:21:59 UTC
Connected default database with reconnect to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
connect:405.45 ms, ociping:0.453 ms, dualping:0.620 ms, sid=5457, inst#=1, time=1.4
connect:323.61 ms, ociping:1.200 ms, dualping:0.606 ms, sid=27681, inst#=1, time=2.3
connect:295.12 ms, ociping:0.551 ms, dualping:0.474 ms, sid=49925, inst#=1, time=3.3
connect:291.14 ms, ociping:0.473 ms, dualping:0.535 ms, sid=46299, inst#=1, time=4.3
connect:284.86 ms, ociping:0.465 ms, dualping:0.538 ms, sid=46316, inst#=1, time=5.3
connect:358.89 ms, ociping:0.523 ms, dualping:0.518 ms, sid=41472, inst#=1, time=6.4
connect:317.53 ms, ociping:0.521 ms, dualping:0.552 ms, sid=6026, inst#=1, time=7.3
connect:340.69 ms, ociping:0.498 ms, dualping:0.490 ms, sid=6026, inst#=1, time=8.3
connect:271.11 ms, ociping:0.499 ms, dualping:0.692 ms, sid=58306, inst#=1, time=9.3
connect mean=320.93, stddev=39.90
ociping mean=0.58, stddev=0.22
dualping mean=0.56, stddev=0.07
If you have an environment where network performance varies, you may need to run connping and/or ociping multiple times to get complete understanding.
Both tools will show help if given a -h option, and both tools take the same arguments. As an example, this is the output from connping -h:
RWP*Connect/OCIPing Release 3.0.2.2 Production on Thu, 11 Aug 2022 09:52:15 UTC
connping - Connect and run OCIPing()
This utility will repetedly establish a new database connection
and use it to perform an OCIPing() and a simple query.
The time to establish connection, do OCIPing and query is output.
-l u/p@c : compulsory option providing database credentials
--period=N : time in seconds run; default 60
--interval=N : interval between each connect/ping, default 1
--no-timestamp : do not include the timestamps
--flush-output : flush stdout after each line
--csvoutput=file : write pure csv to the named file
--flush-csv : flush the csvfile after each line
- The -l option must be provided and is the username, password and connect string (either URL style or entry in tnsnames.ora) to the database. You can omit the password in which case the tool will prompt for it. This is similar to how SQL*Plus works.
- The --period option sets the period for which ociping or connping is running, by default this is 60s.
- The --interval option sets the interval between each output, by default this is 1s.
- If you need to postprocess the output and e.g. plot data using gnuplot, you can use the --csvoutput option, which will write the pure data to a file.
- The --flush-output and --flush-csv file can be used to force flushing of data to respectively stdout and the csv file after each line. This is e.g. useful if you use the tool in a pipeline.
Both tools are available in a single .tgz file for Linux or .zip for file for Microsoft Windows. It can be downloaded from https://github.com/oracle/rwloadsim/releases/latest, where you should get a file such as generated-linux-x86_64-bin-3.1.2.tgz (the actual name will depend on which release is the latest). The file contains the two tools in addition to a few more, which can be put anywhere on the application server or client system from where you want to measure the latency. It is suggested that the binaries are put somewhere in your PATH. An Oracle 19 (or newer) client environment and Oracle Linux 7 (or other distribution with glibc 2.14 or newer) is required. If you are using an older systems, use the file with a name like generated-11-linux-x86_64-bin-3.0.5.tgz, which only requires Oracle 11 and glibc 2.7. For Linux, there are files available for both x68_64 and aarch64 (ARM).
If your system does not already have an Oracle Client, you need to install one, and it is recommended that you install release 19 or newer of Oracle Instant Client.
On some Linux distributions, you may have libnsl.so and/or libaio.so missing. There are details about this at these general installation instructions where you will need to scroll down to the section on Missing Libraries.
For download and installation on Microsoft Windows, please see this wiki page.
If you are using the full installation of rwloadsim in stead of the simple download of the stand-alone binaries, the commands are also available although they are shell scripts rather than binaries. The call is the same such as:
connping -l username/{password}@tnsalias --period=10
With the full installation, you additionally have access to the complete man page, which for e.g. connping is shown if you type:
rwlman connping