How to find number of database round trips by an application

I am a java programmer and I want to know how many database calls/trips are done by my application. We use Oracle as our relational database.

With oracle, I got to know about a way to alter session statistics and generate the trace files. Below are the queries to be fired:

ALTER SESSION SET TIMED_STATISTICS = TRUE;

 ALTER SESSION SET SQL_TRACE = TRUE;

After the trace files are generated, they could be read using the TKProf utility. But this approach cannot be used because:

1. my application uses hibernate and spring frameworks and hence the application does not have an ha开发者_开发知识库ndle to the session.
2. Even if we get the trace files, I need to know whether the set of queries are fired in one go (in a batch) or separately. I am not sure if TkProf output could help to understand this.

Does anyone have any better suggestions?

In TkProf, you can basically tell the number of round-trips as the number of "calls" (although there are exceptions so that less round trips are required, e.g. parse/execute/fetch of a single row select is, theoretically, possible in a single round trip, the so called "exact fetch" feature of oracle). However as a estimate, the tkprof figures are good enough.

If trace wait events, you should directly see the 'SQL*Net from/to client' wait events in the raw trace, but I think tkprof does not show it (not sure, give it a try).

Another way is to look into the session statistics:

select value
  from v$mystat ms, v$statname sn
 where ms.value > 0
   and ms.statistic#=sn.statistic#
   and sn.name IN ('SQL*Net roundtrips to/from client')

However, if you do that in your app, you will slowdown your app, and the figures you receive will include the round-trips for that select.

A wrote a few articles about round-trip optimization:

• http://blog.fatalmind.com/2009/12/22/latency-security-vs-performance/
• http://blog.fatalmind.com/2010/01/29/oracle-jdbc-prefetch-portability/

Firstly, use a dedicated database (or timeframe) for this test, so it doesn't get easily confused with other sessions.

Secondly, look at the view v$session to identify the session(s) for hibernate. The USERNAME, OSUSER, TERMINAL, MACHINE should make this obvious. The SID and SERIAL# columns uniquely identify the session. Actually the SID is unique at any time. The SERIAL# is only needed if you have sessions disconnecting and reconnecting.

Thirdly, use v$sessstat (filtered on the SID,SERIAL# from the v$session) and v$statname (as shown by Markus) to pull out the number of round trips. You can take a snapshot before the test, run the test, then look at the values again and determine the work done.

That said, I'm not sure it is a particularly useful measure in itself. The TKPROF will be more detailed and is much more focussed on time (which is a more useful measure).

Best would be to get a dedicated event 10046 level 12 tracefile of the running session. You will find there all information in detail. This means that you can see how many fetches the application will do per executed command and the related wait events/elapsed time. The resul can be analyzed using tool from Oracle like TKPROF or Oracle Trace Analyzer or Third party tools like [QueryAdvisor][1].

By the way you can ask your DBA to define a database trigger activating Oracle filetrace automatic after login. So capturing the file should not be the problem.

R.U.

[1]: http://www.queryadvisor.com/"TKPROF Oracle tracefile analysis with QueryAdvisor"