How to find the number of calls to the database on the application

Question

I am a Java programmer and I want to know how many database queries / trips are performed by my application. We use Oracle as our relational database.
With oracle, I learned about a way to change session statistics and create trace files. The following are the requests you need to fire:

ALTER SESSION SET TIMED_STATISTICS = TRUE;

 ALTER SESSION SET SQL_TRACE = TRUE;

After creating the trace files, they can be read using the TKProf utility. But this approach cannot be used because:

• my application uses hibernate and spring frameworks and therefore the application does not have a session descriptor.
• Even if we get trace files, I need to know if the query set is started at a time (in the batch version) or separately. I am not sure if the output of TkProf can help to figure this out.

Does anyone have any better suggestions?

Answer 1

In TkProf, you can mainly specify the number of round trips as the number of “calls” (although there are exceptions, so fewer round trips are required, for example, parse / execute / fetch to select one line, theoretically, it is possible in one round-the-world trip, the so-called "exact selection" of the oracle). However, as an estimate, tkprof performance is pretty good.

If there are trace wait events, you should directly see the "SQL * Net from / to client" wait events in the source trace, but I think tkprof does not show this (not sure, try it).

Another way is to view 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')

, , , , , .

A " ":

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

Answer 2

-, ( ) , .

-, v $session, . USERNAME, OSUSER, TERMINAL, MACHINE . SID SERIAL # . SID . SERIAL # , .

-, v $sessstat ( SID, SERIAL # v $) v $statname ( Markus), . , , .

, , . TKPROF ( ).

Answer 3

10046 12 . . , , / . Oracle, TKPROF Oracle Trace Analyzer , [QueryAdvisor] [1].

, , Oracle filetrace automatic . .

R.U.

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