Problem solve Get help with specific problems with your technologies, process and projects.

Getting the SQL traffic byte size for session and transaction

Our Delphi Application runs across a WAN connected to an Oracle database using SQL*Net. I need to know the SQL...

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

Dig Deeper on Oracle database design and architecture