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;

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s