Re: Recording Oracle transaction levels

On Thu, 11 Sep 2008 03:12:53 -0700 (PDT), Johne_uk <edgarj_at_tiscali.co.uk> wrote:

>Hi,
>
>Just a quick question.
>
>My manager is in the process of setting up some KPIs for our IT
>department (mostly general interest for other depts to look at). One
>such metric he has requested is 'the actual number of transactions
>executed within the previous hour'.
>
>I don't think this is possible because, as far as I know, Oracle
>doesn't keep any record of the number of transactions it has executed
>and taking regular snapshots of how many transactions are running at
>that exact time is going to be pretty meaningleess as some
>transactions can be as short as a mill-second.
>
>So the question being - is my assumption above correct or is there
>another way to do this that I'm unaware of?
>
>Thanks in advance
>John

Oracle does keep track of the number of commits and rollbacks, both on session level and systemwide dynamically in v$sysstat and v$sesstat. This info can be captured dynamically by various Oracle provided tools, which are version dependent.
Statspack has been available since 8.1. It is located in $ORACLE_HOME/rdbms/admin, all files starting with sp belong to statspack.
Spauto keeps track of values at regular intervals, spreport calculates the delta between two statspack sessions.

Hth

--