SQL QUERY TO FIND NETWORK STATISTICS IN ORACLE
Use below query to find SQL*Net message from and to client statistics which will be useful to solve latency
select n.name,
round(sum(s.value/1073741824),6)
from v$sesstat s
inner join v$statname n on s.STATISTIC#=n.STATISTIC#
and n.name like '%SQL*Net%'
group by n.name;To find the historical stats of network
select n.stat_name,
round(sum(sy.value/1073741824),6)
from DBA_HIST_SYSSTAT sy
inner join DBA_HIST_STAT_NAME n on sy.STAT_ID=n.STAT_ID
and n.stat_name like '%SQL*Net%'
group by n.stat_name ;
Use osstat details to further troubleshoot TCP buffers at current time
kish@exdbx<>select * from v$osstat where STAT_NAME like '%TCP%';
STAT_NAME VALUE OSSTAT_ID COMMENTS CUMULATIVE
--------------- ---------- ---------- --------------- ---------------
TCP_SEND_SIZE_M 4096 2000 TCP Send Buffer NO
IN Min Size
TCP_SEND_SIZE_D 16384 2001 TCP Send Buffer NO
EFAULT Default Size
TCP_SEND_SIZE_M 4194304 2002 TCP Send Buffer NO
AX Max Size
TCP_RECEIVE_SIZ 4096 2003 TCP Receive Buf NO
E_MIN fer Min Size
TCP_RECEIVE_SIZ 87380 2004 TCP Receive Buf NO
E_DEFAULT fer Default Siz
e
TCP_RECEIVE_SIZ 4194304 2005 TCP Receive Buf NO
E_MAX fer Max Size
6 rows selected.
If you want to analyze the historical stats for TCP buffers, use dba_hist view
kish@exdbx<>select * from dba_hist_osstat where stat_name like '%TCP%';
SNAP_ID DBID INSTANCE_NUMBER STAT_ID STAT_NAME VALUE
---------- ---------- --------------- ---------- --------------- ----------
255 577612197 1 2000 TCP_SEND_SIZE_M 4096
IN
253 577612197 1 2000 TCP_SEND_SIZE_M 4096
IN
258 577612197 1 2000 TCP_SEND_SIZE_M 4096
IN
257 577612197 1 2000 TCP_SEND_SIZE_M 4096
IN
254 577612197 1 2000 TCP_SEND_SIZE_M 4096
IN
256 577612197 1 2000 TCP_SEND_SIZE_M 4096
IN
254 577612197 2 2000 TCP_SEND_SIZE_M 4096
IN
253 577612197 2 2000 TCP_SEND_SIZE_M 4096
IN