How bad is the network round trip when accessing database.

It is a common good practice to save the network round trips when design and develop database applications.
But how bad it is? we need to setup a synthetic benchmark to find out.

My configuration is Oracle database 12.1, Linux 6.4, 64bit, on my laptop virtual box.
There are 2 database servers in one data center. To query remote table through database link.
5000 rows table t2, copy data from view all_objects.

Here is the benchmark results:

• 1 rows, query 1000 times.

6.39 seconds v.s. 0.02 seconds. 10M network.
1.09 seconds v.s. 0.02 seconds. 100M network.

• 1000 rows, row by row fetch v.s. bulk fetch. 100M network.

0.44 seconds : 0 seconds.

So the network latency is between 0.00044 and 0.0011 seconds per round trip.

For example, we have a system process 300 orders per second, run 300 SQL SELECT, 33% time are used for network.
It is considerable amount of time.


Here is the benchmark test setup, so you can reproduce it on your servers.

tnsnames.ora:

mac=
(description=
 (address=(protocol=tcp)(host=192.168.1.104)(port=1521)) 

 (connect_data=(service_name=pdb1)))

SQL>
drop public database link dev1_link;
create public database link dev1_link
connect to scott
identified by abc
using 'mac';

drop public database link dev1_link;
create public database link dev1_link
connect to scott
identified by abc
using 'local';

drop table t2 purge;
create table t2 as select * from all_objects where 1=0;

-- Test 1, remove v.s. local database,

set serveroutput on
declare
 l_cnt number;
 li number;
 g_run1 number;
begin
 l_cnt := 1000;

g_run1 := dbms_utility.get_time;  --This function determines the current time in 100th's of a second.
for i in 1..l_cnt
loop
  select count(*) into li from t2@dev1_link where rownum < 1;
end loop;
g_run1 := (dbms_utility.get_time-g_run1);
dbms_output.put_line( 'remote query ran in '||g_run1);

g_run1 := dbms_utility.get_time;
for i in 1..l_cnt
loop
  select count(*) into li from t2 where rownum < 1;
end loop;
g_run1 := (dbms_utility.get_time-g_run1);
dbms_output.put_line( 'local query ran in '||g_run1);

end;
/

remote query ran in 105
local query ran in 1

set autot on

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
.

-- Test 2, Bulk Fetch, both from remote db,

drop table t2 purge;
create table t2 as select * from all_objects;

set serveroutput on
declare
 l_cnt number;
 li number;
 g_run1 number;
 cursor c1 is select * from t2@dev1_link where rownum <= 1000;
 lr1 c1%rowtype;
 type tab1 is table of c1%rowtype;
 lt1 tab1;
begin
 l_cnt := 1000;

g_run1 := dbms_utility.get_time;  --This function determines the current time in 100th's of a second.

open c1;
loop
  fetch c1 into lr1;
  exit when c1%notfound;
end loop;
g_run1 := (dbms_utility.get_time-g_run1);
dbms_output.put_line( 'remote query ran in '||g_run1);

g_run1 := dbms_utility.get_time;
fetch c1 bulk collect into lt1;
g_run1 := (dbms_utility.get_time-g_run1);
dbms_output.put_line( 'local query ran in '||g_run1);

end;
/

remote query ran in 44

local query ran in 0

.

Let me know your benchmark results, the ball park.

Thanks,
Charlie | Database Architect Developer