By Clemens Bleile

Network latency is crucial for applications, especially if lots of “row by row”-processing (some call it “slow by slow”-processing) is used by the application or if lots of data is transported between the database and the application. If there’s lots of data to be transported between the database and the application then increasing the fetchsize may help to improve the performance and throughput. But this is not the aim of this blog. What I wanted to provide is a simple script on how to meassure the network latency (network-round-trip-time) when running SQL against your database.

In today’s environment with the Cloud and Standby-DBs, it may easily happen that the application server suddenly is far away from the database server. This causes network latency to go up considerably. But what is the network latency exactly between my application server and my database server?

There’s a very good blog from Frits Hoogland describing network latency and how you may measure it in detail.

What I wanted to achieve however, was to provide a simpler method to measure the network latency. The objective was to install sqlcl on the application server and run a SQL with around 5000 network round trips and measure the network latency by calculating

(“Elapsed Time” – “DB Time”) / “network round trips” = Network Latency

Using sqlcl provides the advantage that I just need to have Java on the application server as a prerequisite.

So the first step is to install sqlcl by downloading it from here and unzip it on the Application Server:

REMARK: Download a current version of sqlcl, becuase I’m using “set feedback only” (to hide the query output), which is not available in older versions.

1
2
3
4
5
6
7
8
9
10
$ unzip sqlcl-21.4.0.348.1716.zip
$ alias sqlcl="$PWD/sqlcl/bin/sql"
$ sqlcl /nolog
 
SQLcl: Release 21.4 Production on Wed Dec 29 18:55:47 2021
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
SQL> quit
$

Here the setup-script for my tests:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
$ cat setup_test_netlat_random.sql
 
connect sys/password@clemens-oradb/pdb1 as sysdba
 
set echo on
drop user test_netlat cascade;
create user test_netlat identified by WElcome_netlat_01;
grant dba to test_netlat;
grant select on v_$mystat to test_netlat;
grant select on v_$statname to test_netlat;
grant select on v_$SESS_TIME_MODEL to test_netlat;
 
connect test_netlat/WElcome_netlat_01@clemens-oradb/pdb1
 
drop table tlat purge;
create table tlat (id number, filler varchar2(200));
exec dbms_random.seed(0);
insert into tlat select rownum,dbms_random.string('L',dbms_random.value(100,100)) from all_objects where rownum <= 5000;
commit;
exec dbms_stats.gather_table_stats(user,'TLAT');
 
create or replace function net_roundtrips return number as
nrt number;
begin
   select mystat.value into nrt
   from v$mystat mystat, v$statname statname
   where mystat.statistic# = statname.statistic#
   and statname.display_name = 'SQL*Net roundtrips to/from client';
   return nrt;
end;
/
 
create or replace function my_db_time_microsecs return number as
mydbtime number;
begin
   select value into mydbtime
   from V$SESS_TIME_MODEL
   where sid=SYS_CONTEXT( 'USERENV', 'SID' )
   and stat_name='DB time';
   return mydbtime;
end;
/
 
set echo off

Just run it after adjusting it to your needs:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
$ sqlcl /nolog
 
SQLcl: Release 21.4 Production on Wed Dec 29 20:37:13 2021
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
SQL> @setup_test_netlat_random.sql
Connected.
SQL> drop user test_netlat cascade;
 
User TEST_NETLAT dropped.
 
SQL> create user test_netlat identified by WElcome_netlat_01;
 
User TEST_NETLAT created.
 
SQL> grant dba to test_netlat;
 
Grant succeeded.
 
SQL> grant select on v_$mystat to test_netlat;
 
Grant succeeded.
 
SQL> grant select on v_$statname to test_netlat;
 
Grant succeeded.
 
SQL> grant select on v_$SESS_TIME_MODEL to test_netlat;
 
Grant succeeded.
 
SQL>
SQL> connect test_netlat/WElcome_netlat_01@clemens-oradb/pdb1
Connected.
SQL>
SQL> -- drop table tlat purge;
SQL> create table tlat (id number, filler varchar2(200));
 
Table TLAT created.
 
SQL> exec dbms_random.seed(0);
 
PL/SQL procedure successfully completed.
 
SQL> insert into tlat select rownum,dbms_random.string('L',dbms_random.value(100,100)) from all_objects where rownum  commit;
 
Commit complete.
 
SQL> exec dbms_stats.gather_table_stats(user,'TLAT');
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> create or replace function net_roundtrips return number as
  2  nrt number;
  3  begin
  4     select mystat.value into nrt
  5     from v$mystat mystat, v$statname statname
  6     where mystat.statistic# = statname.statistic#
  7     and statname.display_name = 'SQL*Net roundtrips to/from client';
  8     return nrt;
  9  end;
 10  /
 
Function NET_ROUNDTRIPS compiled
 
SQL>
SQL> create or replace function my_db_time_microsecs return number as
  2  mydbtime number;
  3  begin
  4     select value into mydbtime
  5     from V$SESS_TIME_MODEL
  6     where sid=SYS_CONTEXT( 'USERENV', 'SID' )
  7     and stat_name='DB time';
  8     return mydbtime;
  9  end;
 10  /
 
Function MY_DB_TIME_MICROSECS compiled
 
SQL>
SQL> set echo off
SQL>

I.e. I filled a table with 5000 rows. The filler column contains a 100 Bytes long random string. In addition I created 2 functions which return the current statistics for ‘SQL*Net roundtrips to/from client’ and ‘DB time’ for my session.

Below the script for the network latency test (adjust it to meet your needs as well):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
$ cat test_netlat.sql
connect test_netlat/WElcome_netlat_01@clemens-oradb/pdb1
set echo on
var roundtrips_begin number;
var roundtrips_end number;
var time_begin number;
var time_end number;
var db_time_start number;
var db_time_end number;
set feed only arraysize 15
-- select filler from tlat ;
-- select filler from tlat ;
set arraysize 1
begin
   :roundtrips_begin := NET_ROUNDTRIPS;
   :db_time_start := MY_DB_TIME_MICROSECS;
   :time_begin := dbms_utility.get_time;
end;
/
  
select filler from tlat ;
 
begin
   :roundtrips_end := NET_ROUNDTRIPS;
   :db_time_end := MY_DB_TIME_MICROSECS;
   :time_end := dbms_utility.get_time;
end;
/
 
set serveroutput on feed off
exec dbms_output.put_line(to_char(:roundtrips_end - :roundtrips_begin)||' network round trips.');
exec dbms_output.put_line(to_char((:time_end - :time_begin)*10)||' ms elapsed time.');
exec dbms_output.put_line(to_char((:db_time_end - :db_time_start)/1000)||' ms DB time.');
exec dbms_output.put_line(to_char(round((((:time_end - :time_begin)*10)-((:db_time_end - :db_time_start)/1000))/(:roundtrips_end - :roundtrips_begin),3))||' ms latency per round trip.');
exec dbms_output.put_line('--> (Elapsed Time - DB Time) / network round trips');
set echo off

What I’m basically doing is to get the number of “SQL*Net roundtrips to/from client” and the “DB Time” of my session before the run. In addition I take the begin-time with the dbms_utility.get_time function. With “arraysize 1” (1 row per fetch) I run a full-table-scan to get the 5000 rows – one by one over the network. After taking statistcs after the run I do produce the output:

– number of network round trips
– elapsed time of the query in ms
– DB time of the query in ms
– the calculated network latency: (Elapsed Time – DB Time) / network round trips

Here an example output:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
$ sqlcl /nolog
 
SQLcl: Release 21.4 Production on Wed Dec 29 20:41:26 2021
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
SQL> @test_netlat
Connected.
SQL> var roundtrips_begin number;
SQL> var roundtrips_end number;
SQL> var time_begin number;
SQL> var time_end number;
SQL> var db_time_start number;
SQL> var db_time_end number;
SQL> set feed only arraysize 15
SQL> -- select filler from tlat ;
SQL> -- select filler from tlat ;
SQL> set arraysize 1
SQL> begin
  2     :roundtrips_begin := NET_ROUNDTRIPS;
  3     :db_time_start := MY_DB_TIME_MICROSECS;
  4     :time_begin := dbms_utility.get_time;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> select filler from tlat ;
 
5,000 rows selected.
 
SQL>
SQL> begin
  2     :roundtrips_end := NET_ROUNDTRIPS;
  3     :db_time_end := MY_DB_TIME_MICROSECS;
  4     :time_end := dbms_utility.get_time;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> set serveroutput on feed off
SQL> exec dbms_output.put_line(to_char(:roundtrips_end - :roundtrips_begin)||' network round trips.');
4953 network round trips.
 
SQL> exec dbms_output.put_line(to_char((:time_end - :time_begin)*10)||' ms elapsed time.');
18870 ms elapsed time.
 
SQL> exec dbms_output.put_line(to_char((:db_time_end - :db_time_start)/1000)||' ms DB time.');
876.625 ms DB time.
 
SQL> exec dbms_output.put_line(to_char(round((((:time_end - :time_begin)*10)-((:db_time_end - :db_time_start)/1000))/(:roundtrips_end - :roundtrips_begin),3))||' ms latency per round trip.');
3.633 ms latency per round trip.
 
SQL> exec dbms_output.put_line('--> (Elapsed Time - DB Time) / network round trips');
--> (Elapsed Time - DB Time) / network round trips
 
SQL> set echo off
SQL>

I.e. I do have an average network latency of 3.633 ms for my 100 Bytes of data.

With the tc-utility I can add a 5ms delay for packets crossing my network interface wlxe84e063dd27f on my Linux-Server:

1
% tc qdisc add dev wlxe84e063dd27f root netem delay 5ms

Then repeat the test:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SQL> set arraysize 1
SQL> begin
  2     :roundtrips_begin := NET_ROUNDTRIPS;
  3     :db_time_start := MY_DB_TIME_MICROSECS;
  4     :time_begin := dbms_utility.get_time;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL>
SQL> select filler from tlat ;
 
5,000 rows selected.
 
SQL>
SQL> begin
  2     :roundtrips_end := NET_ROUNDTRIPS;
  3     :db_time_end := MY_DB_TIME_MICROSECS;
  4     :time_end := dbms_utility.get_time;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL> set serveroutput on feed off
SQL> exec dbms_output.put_line(to_char(:roundtrips_end - :roundtrips_begin)||' network round trips.');
4953 network round trips.
 
SQL> exec dbms_output.put_line(to_char((:time_end - :time_begin)*10)||' ms elapsed time.');
45050 ms elapsed time.
 
SQL> exec dbms_output.put_line(to_char((:db_time_end - :db_time_start)/1000)||' ms DB time.');
747.075 ms DB time.
 
SQL> exec dbms_output.put_line(to_char(round((((:time_end - :time_begin)*10)-((:db_time_end - :db_time_start)/1000))/(:roundtrips_end - :roundtrips_begin),3))||' ms latency per round trip.');
8.945 ms latency per round trip.
 
SQL> exec dbms_output.put_line('--> (Elapsed Time - DB Time) / network round trips');
--> (Elapsed Time - DB Time) / network round trips

The latency went up from 3.633 ms on the first run to 8.945 ms on the second run.

The results have been confirmed with the oratcptest-tool from MOS Note “Assessing and Tuning Network Performance for Data Guard and RMAN (Doc ID 2064368.1)”:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ java -jar oratcptest.jar clemens-oradb -mode=sync -length=100 -duration=20s -interval=20s -port=1550
[Requesting a test]
   Message payload        = 100 bytes
   Payload content type   = RANDOM
   Delay between messages = NO
   Number of connections  = 1
   Socket send buffer     = (system default)
   Transport mode         = SYNC
   Disk write             = NO
   Statistics interval    = 20 seconds
   Test duration          = 20 seconds
   Test frequency         = NO
   Network Timeout        = NO
   (1 Mbyte = 1024x1024 bytes)
 
(21:00:52) The server is ready.
                    Throughput             Latency
(21:01:12)      0.027 Mbytes/s            3.901 ms
(21:01:12) Test finished.
          Socket send buffer = 131768 bytes
             Avg. throughput = 0.027 Mbytes/s
                Avg. latency = 3.901 ms

With the 5ms additional latency:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
$ java -jar oratcptest.jar clemens-oradb -mode=sync -length=100 -duration=20s -interval=20s -port=1550
[Requesting a test]
   Message payload        = 100 bytes
   Payload content type   = RANDOM
   Delay between messages = NO
   Number of connections  = 1
   Socket send buffer     = (system default)
   Transport mode         = SYNC
   Disk write             = NO
   Statistics interval    = 20 seconds
   Test duration          = 20 seconds
   Test frequency         = NO
   Network Timeout        = NO
   (1 Mbyte = 1024x1024 bytes)
 
(21:02:05) The server is ready.
                    Throughput             Latency
(21:02:25)      0.011 Mbytes/s            9.293 ms
(21:02:25) Test finished.
          Socket send buffer = 131768 bytes
             Avg. throughput = 0.011 Mbytes/s
                Avg. latency = 9.293 ms

To cleanup just drop the DB-user test_netlat:

1
SQL> drop user test_netlat cascade;

and (if set) remove the network delay on the network card:

1
% tc qdisc del dev wlxe84e063dd27f root

Summary: If you plan to have a longer distance between the application-server and the db-server (e.g. when going to the Cloud) then test the latency carefully. If there is a high number of network round trips between the application and the database then chances are high that the application becomes MUCH slower after moving the servers away from each other. Here the statistics about network round trips in your database since startup:

1
2
3
SQL> select value from v$sysstat sysstat, v$statname statname
  2  where sysstat.statistic# = statname.statistic#
  3  and statname.display_name = 'SQL*Net roundtrips to/from client';

REMARK 1: There’s a second Blog Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl explaining the difference of number of network round trips between sqlcl and sqlplus when running the script of this Blog.

REMARK 2: Oracle’s “Realworld Performance Team” published a tool ociping to measure network latency as well. See here for details. Please consider that you need an Oracle Client installation for ociping to run.

Post Views: 4,950
Thumbnail [60x60]
by
Clemens Bleile