-
Notifications
You must be signed in to change notification settings - Fork 19
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 and output is:
ociping -l username/{password}@//host/machine --period=10
RWP*OCIPing Release 3.0.2.2 Production on Thu, 11 Aug 2022 09:50:37 UTC
Connected default database to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
0.450 0.0
0.502 1.0
0.669 2.0
0.473 3.0
0.494 4.0
0.502 5.0
0.514 6.0
0.443 7.0
0.742 8.0
0.463 9.0
ociping mean=0.525 stddev=0.094
Note that the above call with the executable call ociping is assuming you have the stand-alone download. If you have the full installation, you should use rwloadsim with the -u option to find the ociping.rwl script and the above call would be:
rwloadsim -u ociping.rwl -l username/{password}@//host/machine --period=10
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 and 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
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 that can be downloaded from https://github.com/oracle/rwloadsim/releases/tag/v3.0.2, where you should get generated-linux-x86_64-bin-3.0.2.tgz. The file just contains the two tools, 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 generated-11-linux-x86_64-bin-3.0.2.4.tgz, which only requires Oracle 11 and glibc 2.7.
If you are using the full installation of rwloadsim in stead of the simple download of the stand-alone binaries, the binaries may not exist. In that case, you need to call rwloadsim with the -u option to find the actual rwl file. For the first ociping example above, this would mean:
rwloadsim -u ociping.rwl -l username/{password}@//host/machine --period=10