Data transfers through SQL*Net

We need to check how much data is going back and forth from database servers to take some system engineering decisions. In order to know what is the flow rate for current sessions, I issued the following SQL -

/** Current Sessions **/

SELECT n.name,ss.username, ROUND((SUM(st.value))/1024/1024) MB
FROM v$sesstat st,v$session ss ,v$statname n
WHERE ss.SID=st.SID
AND n.statistic# = st.statistic#
AND n.name like '%SQL*Net%'
HAVING ROUND((SUM(st.value))/1024/1024) > 0
GROUP BY N.NAME,ss.username
ORDER BY ss.username, n.name;

NAME	USERNAME	MB
---------------------------	--------------	-----------
SQL*Net roundtrips to/from client	DBSNM	1
bytes received via SQL*Net from client	DBSNM	182
bytes sent via SQL*Net to client	DBSNM	154
bytes received via SQL*Net from client	PROD	68
bytes sent via SQL*Net to client	PROD	141

Well, how do I know the historical data for the instance? The answer could be like follows -

/** Since Instance Startup **/

SELECT NAME,ROUND(VALUE/1024/1024) MB
FROM v$sysstat
WHERE NAME like '%SQL*Net%'
AND ROUND(VALUE/1024/1024) > 0
ORDER BY name;

NAME	MB
-------------------------------------	-------------------
SQL*Net roundtrips to/from client	380
bytes received via SQL*Net from client	46331
bytes sent via SQL*Net to client	92414

There are other ways to do that from the trace files. If I enable trace level by adding the following lines in my listener.ora and reload it, then I would find my desired stuffs in the trace file.

-- listener.ora

TRACE_FILE_LISTENER = netstat-info-20080409.trc
TRACE_DIRECTORY_LISTENER = /export/home/oracle
TRACE_LEVEL_LISTENER =SUPPORT

From command line as oracle user -
# lsnrctl reload

Now it's time to check the logs after certain period -

# trcasst -s /export/home/oracle/netstat-info-20080409.trc

The trcasst just came up with the nice formatted output - cool !


*************************************************************************
* Trace Assistant *
*************************************************************************

----------------------
Trace File Statistics:
----------------------
Start Timestamp : 09-APR-2008 13:03:10:531
End Timestamp : 09-APR-2008 19:54:36:028
Total number of Sessions: 1172

DATABASE:
Operation Count: 0 OPENS, 0 PARSES, 0 EXECUTES, 0 FETCHES


ORACLE NET SERVICES:
Total Calls : 1593 sent, 1427 received, 0 oci
Total Bytes : 292573 sent, 255785 received
Average Bytes: 183 sent per packet, 179 received per packet
Maximum Bytes: 2011 sent, 2034 received

Grand Total Packets: 1593 sent, 1427 received


*************************************************************************
* Trace Assistant has completed *
*************************************************************************