Re: measuring TPM

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Wed, 28 Jan 2004 08:09:27 -0800
Message-ID: <F001.005DE499.20040128080927@fatcity.com>

Well, as you are well aware of, you cannot measure without impacting. I know of the following methods:

1. Turn on auditing, count all transactions from dba_audit_trail table within a day and divide by the number of minutes in 9 hours. That will give you an average TPM number during the working hours. The problem is that auditing will impact the transaction rate.
2. Pick a single user, a chosen "average Joe" (or Josephine, to to avoid accusations for gender bias), create a logon trigger which will record "user commits" from v$sesstat and that will be the number of transactions. Divide by the number of minutes and multiply by the number of users on your system. The problem with this method is that it is usually very hard to pick up an average overall user of the system, so the whole thing is performed by department.
3. Count user commits in v$sysstat, which will count them system-wide. Divide by period. The query would go like this:

SQL> select name, value from v$sysstat
  2 where name = 'user commits';

NAME VALUE

---

---

user commits 1

On 01/28/2004 10:29:25 AM, Charlie_Mengler_at_HomeDepot.com wrote:
>
> I've been asked to provide value for the Transactions Per Minute
> going through our primary OLTP production database.
>
> I believe I can use deltas in SCN values to measure "transactions"
> which do INSERT/UPDATE/DELETE and then COMMIT;
>
> Is there any way to measure/count the number of SELECTs which occur?
> If so, how?
>
> How would you derive a value for TPM for your DB?
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Charlie_Mengler_at_HomeDepot.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jan 28 2004 - 10:09:27 CST