Ask The Real Tom

The application and the db are running on the same server. Now we want to move the application to a different server. Therefor we identified the network as possible bottleneck.

The only way to get the traffic seem to be a sampling of the values from v$sysstat.

select value from v$sysstat where name like 'bytes received via SQL*Net from client';
select value from v$sysstat where name like 'bytes sent via SQL*Net to client';

To meassure the traffic between application and database I made a small script.

#!/bin/ksh
#
# Author: Thomas Rieder
# meassure oracle net traffic
#
while true; do
DATE=$(date +%Y%m%d%H%M)
sqlplus -S / as sysdba <<_EOF
set serveroutput on size 1000000
alter session set nls_date_format='YYYYMMDDHH24MiSS';
spool oracle_traffic_$DATE.log
declare
v_timestamp date;
v_recieved number;
v_send number;
begin
for c in 1..900 loop -- 15 min
select
sysdate,
(select value from v\$sysstat where name like 'bytes received via SQL*Net from client') recieved,
(select value from v\$sysstat where name like 'bytes sent via SQL*Net to client') sent
INTO v_timestamp,v_recieved,v_send
from dual;
begin
dbms_output.put_line(v_timestamp||';'||v_recieved||';'||v_send);
end;
DBMS_LOCK.sleep(1);
end loop;
end;
/
spool off
_EOF
done;


It should log every 15 minutes a the time;recieved bytes; send bytes over sqlnet.