{"id":488,"date":"2021-12-29T21:13:27","date_gmt":"2021-12-29T20:13:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/2021\/12\/29\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/"},"modified":"2023-01-23T16:40:18","modified_gmt":"2023-01-23T15:40:18","slug":"script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/","title":{"rendered":"Script to calculate the network latency between the application and the Oracle DB-server"},"content":{"rendered":"

By Clemens Bleile<\/h2>\n

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.<\/p>\n

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?<\/p>\n

There’s a very good blog from Frits Hoogland<\/a> describing network latency and how you may measure it in detail.<\/p>\n

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<\/p>\n

(“Elapsed Time” – “DB Time”) \/ “network round trips” = Network Latency<\/p>\n

Using sqlcl provides the advantage that I just need to have Java on the application server as a prerequisite.<\/p>\n

So the first step is to install sqlcl by downloading it from here<\/a> and unzip it on the Application Server:<\/p>\n

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.<\/p>\n

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

Here the setup-script for my tests:<\/p>\n

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

Just run it after adjusting it to your needs:<\/p>\n

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

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.<\/p>\n

Below the script for the network latency test (adjust it to meet your needs as well):<\/p>\n

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

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:<\/p>\n

– 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<\/p>\n

Here an example output:<\/p>\n

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

I.e. I do have an average network latency of 3.633 ms for my 100 Bytes of data.<\/p>\n

With the tc-utility I can add a 5ms delay for packets crossing my network interface wlxe84e063dd27f on my Linux-Server:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
% tc qdisc add dev wlxe84e063dd27f root netem delay 5ms<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

Then repeat the test:<\/p>\n

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

The latency went up from 3.633 ms on the first run to 8.945 ms on the second run.<\/p>\n

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)”:<\/p>\n

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

With the 5ms additional latency:<\/p>\n

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

To cleanup just drop the DB-user test_netlat:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>drop<\/code> user<\/code> test_netlat <\/code>cascade<\/code>;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

and (if set) remove the network delay on the network card:<\/p>\n

\n
\n\n\n\n
\n
1<\/div>\n<\/td>\n
\n
\n
% tc qdisc del dev wlxe84e063dd27f root<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

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:<\/h3>\n
\n
\n\n\n\n
\n
1<\/div>\n
2<\/div>\n
3<\/div>\n<\/td>\n
\n
\n
SQL> <\/code>select<\/code> value <\/code>from<\/code> v$sysstat sysstat, v$statname statname<\/code><\/div>\n
\u00a0\u00a0<\/code>2\u00a0 <\/code>where<\/code> sysstat.statistic# = statname.statistic#<\/code><\/div>\n
\u00a0\u00a0<\/code>3\u00a0 <\/code>and<\/code> statname.display_name = <\/code>'SQL*Net roundtrips to\/from client'<\/code>;<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n\n\n

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

REMARK 2: Oracle’s “Realworld Performance Team” published a tool ociping to measure network latency as well. See here<\/a> for details. Please consider that you need an Oracle Client installation for ociping to run.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[106,107,108],"type_dbi":[],"class_list":["post-488","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-between-application-and-db-server","tag-network-latency","tag-roundtrip"],"acf":[],"yoast_head":"\nScript to calculate the network latency between the application and the Oracle DB-server - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Script to calculate the network latency between the application and the Oracle DB-server\" \/>\n<meta property=\"og:description\" content=\"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 […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-12-29T20:13:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-01-23T15:40:18+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Script to calculate the network latency between the application and the Oracle DB-server\",\"datePublished\":\"2021-12-29T20:13:27+00:00\",\"dateModified\":\"2023-01-23T15:40:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/\"},\"wordCount\":731,\"commentCount\":0,\"keywords\":[\"between application and db-server\",\"network latency\",\"roundtrip\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/\",\"name\":\"Script to calculate the network latency between the application and the Oracle DB-server - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2021-12-29T20:13:27+00:00\",\"dateModified\":\"2023-01-23T15:40:18+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Script to calculate the network latency between the application and the Oracle DB-server\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\\\/\\\/www.dbi-services.com\",\"https:\\\/\\\/x.com\\\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/clemens-bleile\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Script to calculate the network latency between the application and the Oracle DB-server - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/","og_locale":"en_US","og_type":"article","og_title":"Script to calculate the network latency between the application and the Oracle DB-server","og_description":"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 […]","og_url":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/","og_site_name":"dbi Blog","article_published_time":"2021-12-29T20:13:27+00:00","article_modified_time":"2023-01-23T15:40:18+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Script to calculate the network latency between the application and the Oracle DB-server","datePublished":"2021-12-29T20:13:27+00:00","dateModified":"2023-01-23T15:40:18+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/"},"wordCount":731,"commentCount":0,"keywords":["between application and db-server","network latency","roundtrip"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/","url":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/","name":"Script to calculate the network latency between the application and the Oracle DB-server - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-12-29T20:13:27+00:00","dateModified":"2023-01-23T15:40:18+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Script to calculate the network latency between the application and the Oracle DB-server"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/488","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=488"}],"version-history":[{"count":3,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/488\/revisions"}],"predecessor-version":[{"id":21925,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/488\/revisions\/21925"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=488"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=488"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=488"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=488"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}