-
Notifications
You must be signed in to change notification settings - Fork 18
Measuring Oracle Net Throughput
Many database application transfer large amounts of data between the database and some client or application server, and often does that using one or many concurrent "streams" of data. This is in particular the case for data warehouse style applications that import or export to or from the database. There are often figures available for the transfer speeds of the physical network, but in addition to these, getting an actual measure on the "database wire" is in many cases valuable information.
As part of the RWP*Load Simulator, there is a tool that estimates the throughput from the database to the client. In addition to being part of the complete RWP*Load Simulator, this tool is also available completely stand-alone.
The netthroughput tool measures throughput by executing a query with the following properties:
- Each row returned is close to 16kB in size.
- Although not guaranteed random, each row is different from the previous and has a high entropy.
- The database time (purely CPU) is small compared to the network time such that the network time dominates.
- The array interface is used receiving a total of 64 rows or close to 1MB.
- The process is repeated to have sufficient data to give a reasonable estimate of actual throughput.
- One or more concurrent streams, each with their own database connection, are executed. The goal of this approach is to ensure network traffic dominates such that calculations of the transfer speed can be expected to truly represent Oracle Net throughput. The (almost) random and high entropy nature of the data should also ensure no or only little compression takes place.
To use the tool, credentials to connect to the database (username, password, connect string) are needed, and if these allow queries to v$ views, the results will include values based on physical byte counts. Otherwise, only ability to connect to the database is needed and no schema objects are created or used.
A sample call and output is:
netthroughput -l username/{password}@//host/db --period=20 --concurrency=2
RWP*Load Simulator Release 3.0.2.11 Development on Wed, 14 Sep 2022 09:36:56 UTC
Connected default database to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Connecting sessions
All threads connected
Actual throughput test now running 20 s
Throughput estimate 0.248 GB/s logical, 0.248 GB/s physical
The tool is documented using rwlman nettrhoughput if you have a full installation of
the RWP*Load Simulator. Otherwise, if you have a download of the generated executable, it
can be provided a -h option, which produces this output:
RWP*Net Throughput Release 3.0.2.11 Development on Wed, 14 Sep 2022 14:51:47 UTC
netthroughput - Measure Oracle Net throughput from database to client
This utility makes a database connection and runs queries sending large
data amounts back to the client and use that to measure the throughput.
You provide a number of concurrent streams doing this and can by increasing
the count get an estimate of the maximum throughput available.
If there is access to v$mystat, both physical and logical throughput
will be output; otherwise only logical is available.
-l u/p@c : Compulsory option providing database credentials
--period=N : Set run period, default is 60s
--verbosetest : Make output more verbose
--concurrency=N : Run this many concurrent throughput tests, default 1
--fetcharray=N : Use this array size, default 8 meaning ~100kB in each array fetch
--rampup=N : Set rampup period for throughput tests, default is 5s
--mbperquery=M : Change the default of 1MB per query
--csvoutput=file : Write pure csv data to this file
--csvappend=file : Append pure csv data to this file
--csvexcel : Use ';' field separator and CRLF line terminator in csv file
If you create a csv file, it always has four columns:
1: the value of concurrency
2: the logical throughput in MB/s
3: the physical throughput in MB/s or 0 if not available
4: 0 if data is reliable, otherwise the needed rampup
- The -l option is compulsory 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.
- By default, the test runs for 60s, the --period option can be used to change this.
- By default, only one stream executes the query. To use multiple streams, use the --concurrency option.
- If you want the actual network packages to be different from the roughly 100kB, use --fetcharray with a value different from 8. You can similarly change the total size of each query using the --mbperquery option.
- When multiple concurrent streams are running, it is important that the start and therefore run at exactly the same time. If rampup is slow, the tool will warn about some streams being late and suggest a new value for the --rampup parameter.
- For generating csv files, see below.
You typically need to perform multiple executions with increasing values for the --concurrency option. To further analyze data from doing this, you can use the various csv options to create a csv file with the data. One suggestion is to use a loop like:
for cur in 2 4 6 8 10 12 14 16 18 20
do
netthroughput --csvappend=mytp.csv -l username/{password}@//host/db --concurrency=$cur
done
After execution, the generated mytp.csv file may contain:
#streams logicalMB/s physicalMB/s rampupneed
2 145.714 145.698 0
4 308.625 308.590 0
6 487.931 487.877 0
8 635.595 635.524 0
10 811.702 811.612 0
12 962.215 962.108 0
14 1140.971 1140.845 0
16 1284.486 1284.344 0
18 1454.045 1453.883 0
20 1625.603 1625.422 0
which potentially could be used as input to gnuplot. If you additionally provide the --csvexcel option, the format will be useful for processing using typical office software on a Windows PC.
Note that each execution of the tool writes one line of data to the csv file and that each line contains these four values:
- The concurrency used.
- The logical throughput in MB/s, i.e. based on the amount of data received by the query executed.
- The physical throughput in MB/s which is based on queries against v$ tables for the statistics 'bytes sent via SQL*Net to client'. If the necessary v$ views cannot be accessed, the value will be 0.
- A value of 0 indicates the results are valid and that all concurrent streams started at the same time. A non zero value indicates some streams were late in starting making results unreliable. The actual value is the required value for the --rampup parameter.
The tool has a relatively high resource consumption both on the database, on the client where it is executed, and on the network. Although it is assumed that increased concurrency eventually will result in the network being the bottleneck, this cannot be guaranteed. If possible, it is therefore suggested to run "top" or similar on both the client and the database server to ensure neither of these reach cpu exhaustion.
The command is available in both the full rwloadsim installation and in the stand alone binary distribution. With the full installation, you additionally have access to the complete man page if you type:
rwlman netthroughput
See https://github.com/oracle/rwloadsim/wiki/Measuring-Oracle-Net-Latency#download for download details.