Ask The Real Tom

June 12, 2008



Meassure SQL*Net Traffic from an Oracle Instance

Filed under: oracle — Tags: , , — admin @ 6:24 pm

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.

Powered by WordPress