Thursday, January 27, 2011

How to get number of calls to Oracle DB

I want to monitor number of calls made by my application Oracle DB. Which counter (from V$table) should I use ?

  • I'd probably start with this, though you would join to v$session rather than v$mystat, and filter whichever sessions belong to your application. But this may 'undercount' if sessions get disconnected when they aren't being used.

    select sid, name, value from v$sesstat s 
      join v$statname n on n.statistic# = s.statistic#
    where s.sid in (select sid from v$mystat where rownum=1)
    and value > 0
    and name in ('user calls')
    order by value desc;
    

    Also, I'm not sure sure that calls are a particularly good metric. I'd go for 'consistent gets' if I had to pick just one (basically a database block read).

    Final caveat. Metrics can't tell the difference between "We are doing more work because of code/config/whatever problems" and "we are doing more work in the database because we are doing more business with our customers".

    From Gary

0 comments:

Post a Comment