FW: number of transactions per minute/hour

Here is a TPS calculation query that I have posted on metalink a couple of times. For those who wonder what the comment says the translation is, "create as response to metalink request from version 7 query."

set echo off
--

• SQL*Plus script to calculate Transactions Per Second for version 8+
  --
  
• 20020513 Mark D Powell New, cre as resp 2 metalink req fr ver 7 Query
  --
  
• Version 7 Query:
• SELECT SUM(s.value/
• (86400*(SYSDATE - TO_DATE(i.VALUE,'J')))) "tps"
• FROM V$SYSSTAT s, V$INSTANCE i
• WHERE s.NAME in ('user commits','transaction rollbacks')
• AND i.KEY = 'STARTUP TIME - JULIAN'
  --
  select round(sum(s.value / (86400 * (SYSDATE - startup_time))),3) "TPS" from v$sysstat s ,v$instance i where s.NAME in ('user commits','transaction rollbacks') /

Being that the OP wanted to count selects I do not see a solution to the original request.
-- Mark D Powell --

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jesse, Rich Sent: Friday, September 15, 2006 9:41 AM To: oracle-l_at_freelists.org
Subject: RE: number of transactions per minute/hour

Admittedly without much homework (that I can remember), I've defined a transaction as:

 SELECT SUM(VALUE)
 FROM sys.v_$sysstat
 WHERE NAME IN ('user commits','user rollbacks');

...which of course is only valuable when the delta of two results is measured over time. But I would presume that this would have to take into account all transactions, including those containing "recursive SQL". What is the purpose of the data collection where recursive SQL bothers you? If it's to compare the results of business processes, the overhead of DD calls is valid, isn't it? Just curious...

Rich

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christian Antognini Sent: Friday, September 15, 2006 2:31 AM To: exriscer_at_gmail.com
Cc: oracle-l_at_freelists.org; ax.mount_at_gmail.com Subject: RE: number of transactions per minute/hour

Hi

> Not sure but I think it is very tedious to get those figures.... I 
> always wondered how benchmark tools gives you informations such as 
> transaction per minute, I think those tools dont use v$sysstat for 
> these purposes

The reason is quite simple. DML statements or queries are *not* transactions! For that reason Anjo asked the OP to give *his* definition of "USER transaction"... For a regular definition have a look here: http://en.wikipedia.org/wiki/Database_transaction.

Regards,
Chris
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Sep 15 2006 - 09:07:31 CDT