Oracle Database 19c -- Identifying Network Latencies Between A Client and A Database

Problem

You want to identify the network latency between a client and a database. the ping or other standard network utilities are not available.

 

Solution

The Oracle Database provides the "SQL*Net message from client" wait event, which is an idle wait event, representing the idle time the server process spent waiting for data from the client. From the client perspective, if the client was keeping busy to send data to the server process, the time denoted by the
"SQL*Net message from client" wait event would indicate the time wasted between the client and the database, which is network layer, including firewall, proxies etc.

The exercise will use SQL Tracing Facility and TKPROF reports to identify
the "SQL*Net message from client" wait event and time spent outside the database.

 

1. Create an external table to access the trace files as described in Oracle Database 12c -- Access SQL Trace Files As An External Table

 

 

2. Create and execute the following sqlplus script from the client session:

sqlplus> host vi script.sql

 

set termout on

set heading off

set feedback off

set newpage none

col x noprint

prompt Spooling into tempscript.sql

set termout off

spool tempscript.sql

prompt exec dbms_session.session_trace_enable;

select 'select rpad(''x'', 4000, ''x'') x from dual;' from dual connect by level<=100;

prompt exec dbms_session.session_trace_disable;

spool off

set termout on

prompt Executing script...

set termout off

@@tempscript

 

sqlplus> @script

This will enable sql trace, and make the session keep sending and receiving data. Any time spent in between will denote network latencies. At the end of the workload, the script will disable sql trace.

 

3. On the client side, alter the external table to access the generated trace file, and calculate the latencies: 

col trace_file new_val trace_file
select substr(value,instr(value,'/',-1)+1) as trace_file from v$diag_info where name = 'Default Trace File';
alter table trace_file location('&trace_file');

set heading on

set sqlp ""

set sqln off

select delta "Latency in microseconds", count(*) from (

select line, round(curr-prev, -3) delta from (

select line, curr, lag(curr, 1, 0) ignore nulls over (order by rownum) prev from (

select rownum, line, regexp_substr(line, 'tim=(\d+)', 1, 1, 'i', 1) curr from trace_file)))

where line like '%message from client%'

group by delta;

set sqlp "SQL> "