Getting the SQL traffic byte size for session and transaction

traffic byte size between the application and the database for the session and per transaction (i.e. bytes sent via SQL*Net to client and bytes recieved via SQL*Net from client).

Luckily, Oracle captures this metric. You can query V$SYSSTAT similar to the following:

SELECT name,value 
FROM v$sysstat
WHERE name IN ('bytes sent via SQL*Net to
client','bytes received via SQL*Net from client');

This will give you SQL net traffic to and from all clients for the life of the database.

Similarly, you can get the same data for a particular session by issuing a query similar to this:

SELECT n.name,m.value
FROM v$mystat m, v$statname n
WHERE m.statistic#=n.statistic#
AND m.sid=XX
AND n.name IN ('bytes sent via SQL*Net to
client','bytes received via SQL*Net from client');

You will have to substitute a value for 'XX' in the above statement for the session in question.

But I suspect that you already knew all of this since you used the exact statistic names in your question. So to answer the rest of your question...If you wish to know the number of bytes transferred to each session, you will have to code your application to get this information before the application session ends. Then store this information in a table so that you can view it later. If you do not store this information, the statistics will be lost once the session completes.

If you wish to know this information for each transaction, then you will have even more difficulty. The Oracle database does not capture this information on a transaction level. So you will have to query for this information before and after each transaction. Compute the difference to see how much the transaction contributed to the statistic. But keep in mind that your queries will contribute to this statistic as well.

For More Information

• Dozens more answers to tough Oracle questions from Brian Peasland are available.
• The Best Oracle Web Links: tips, tutorials, scripts, and more.
• Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.