txn per second

[hallibud]

I am in the process of gathering information related to our database.
Does anyone know of a way to get transactions per second? I've looked into statspack but
it doesn't supply what I'm looking for.
Any information would be greatly appreciated.

[jmodic]

select sysdate, sum(value) from v$sysstat
where name in ('user commits', 'user rollbacks');

Run the above query at the beginning and at the end of the desired measuring interval, subtract the first result from the second, divide this by the number of elapsed seconds and you'll get your system's average number of transactions per second.

[WROOS]

If you're as lazy as I am, you would run:

SELECT SUM(s.value/(86400*(SYSDATE - startup_time))) tps
FROM v$sysstat s, v$instance i
WHERE s.name IN ('user commits','transaction rollbacks')

It eliminates the dividing.

[jmodic]

Originally posted by WROOS
If you're as lazy as I am, you would run:

SELECT SUM(s.value/(86400*(SYSDATE - startup_time))) tps
FROM v$sysstat s, v$instance i
WHERE s.name IN ('user commits','transaction rollbacks')

It eliminates the dividing.

Good point. However if your instance has been up for a very long time and you have a very bussy system you might get totaly wrong result with the above query. VALUE field in V$SYSSTAT has quite limited scope, although it is simpl reported yas NUMBER if you do DESCRIBE V$SYSSTAT. So if your instance is up and running for a long time and you have very frequent transactions the VALUE number will fill up and will start to count from zero again.....