RE: measuring TPM
And these would be CPM (commits per minute) and RPM (rollbacks per minute). If you really want a transaction, you have to code it yourself, otherwise all you can get is CPM and RPM. If your transactions_per_minute ( or commits_per_minute) is low use this handy script to bump it up. create table my_dual as select * from dual / begin for i in 1 .. 1000 loop insert into my_dual values(i); commit; delete from my_dual; commit; end loop; end; / add salt and pepper to taste, serve with nice red wine, enjoy. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Mladen Gogala Sent: Wednesday, January 28, 2004 11:09 AM To: Multiple recipients of list ORACLE-L 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. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: measuring TPM
Charlie, I understand a transaction as a succession of SQL statements between two successive COMMITs or ROLLBACKs - you will find inside V$SYSSTAT how many COMMITs and ROLLBACKs were issued. If you are interested, besides transactions proper, in the number of statements executed, then have a look at 'execute count'. You also have stats to tell you how many of them were recursive statements I believe. Talking about metrics (and forgetting about what you have been asked to provide :-)), methinks you can have a reasonably fair (and balanced) view of what is going on by collecting six values : o Number of sessions and number of executions to see what users are asking of your database o Redo blocks written to see the 'update' activity and the number of bytes sent which roughly tell you what users want to be done o Physical and logical I/Os to see how efficiently it is done Discrepancies should trigger investigation. HTH, Stephane Faroult - --- Original Message --- - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wed, 28 Jan 2004 07:29:25 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: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: measuring TPM
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, [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: measuring TPM
Check out 'user commits','user rollbacks' and (maybe) 'user calls' in v$sysstat. These get collected by statspack so you can plot a chart over time. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 28 January 2004 15:29 To: Multiple recipients of list ORACLE-L Subject: measuring TPM 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: measuring TPM
Charlie, I use the following to determine this: EXEC SQL SELECT ROUND(VALUE/((SYSDATE-STARTUP_TIME)*1440),1) INTO :tp FROM V$SYSSTAT, V$INSTANCE WHERE NAME='user commits'; Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 10:29 AM To: Multiple recipients of list ORACLE-L 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: measuring TPM
My reply would be something along the lines of A transaction as you would like it to be measured is best measured in the application. I can provide you with IO per minute, broken down into reads and writes, and a number of other statistics. What they are asking for cannot be measured from database statistics, as the oracle concept of a transaction is a unit of work terminated by a COMMIT or ROLLBACK. eg. SAP can provide the type of metrics they want via its BASIS admin utilities. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/28/2004 07:29 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:measuring TPM 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: measuring TPM
Charlie, What is the perceived relevance of gaining this information? You would be much better off correlating statistics such as overall non idle wait time and database workload (# Users, Ion's/CPU etc...) to actual business functions the database is performing (invoices, sales orders, etc...). I could easily go write a job that doubles the total number of transactions per minute but has almost no effect on the other items which actually correlate application performance to database performance. Thanks, Ethan -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 28, 2004 9:29 AM To: Multiple recipients of list ORACLE-L 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: measuring TPM
On 01/28/2004 12:34:26 PM, Post, Ethan wrote: Charlie, What is the perceived relevance of gaining this information? The information is necessary so that manager and director can make a lovely excell spreadsheet for the VP, who will, in turn, insert it into a slide show for the CIO. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: measuring TPM
If that is what this is for, the formula is very simple. TPM = x*42 where x is a number sufficient to justify the really cool hardware system you want. Mladen Gogala wrote: On 01/28/2004 12:34:26 PM, Post, Ethan wrote: Charlie, What is the perceived relevance of gaining this information? The information is necessary so that manager and director can make a lovely excell spreadsheet for the VP, who will, in turn, insert it into a slide show for the CIO. -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: measuring TPM
Hey Charlie,
I made a DBMS_JOB here that runs this procedure every 5 minutes:
CREATE OR REPLACE PROCEDURE QT_TX_MONITOR AS
-- 06/17/2001 REJesse Created.
v_value NUMBER;
BEGIN
SELECT SUM(VALUE) INTO v_value
FROM V$SYSSTAT
WHERE NAME IN ('user commits','user rollbacks');
INSERT INTO QT_TRANSACTION_LOG
(TX_COUNT, TIMESTAMP)
VALUES (v_value, SYSDATE);
COMMIT;
END QT_TX_MONITOR;
Then, to see the TPM for a given time period, 1 to 2:
SELECT TIMESTAMP, TPM
FROM
(
SELECT TO_CHAR(TIMESTAMP,'MM/DD/ HH24:MI') TIMESTAMP,
TO_CHAR(TX_COUNT - LAG(TX_COUNT) OVER (ORDER BY TIMESTAMP)) TPM
FROM QT_TRANSACTION_LOG
WHERE TIMESTAMP = TO_DATE('1','MM/DD/:HH24:MI')
AND TIMESTAMP = TO_DATE('2','MM/DD/:HH24:MI')
)
WHERE TPM IS NOT NULL
ORDER BY 1;
I use this output to feed into GNUPlot to see the TPM as well as see if a
dev over COMMITs in a batch procedure. The latter shows up as prominent
spikes in the pretty graph.
Don't know if this'll help, but maybe it's a place to start.
GL!
Rich
Rich JesseSystem/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
p.s. When will the Home Depot in West Bend, WI be built? :)
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 28, 2004 9:29 AM
To: Multiple recipients of list ORACLE-L
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: Jesse, Rich
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: measuring TPM
Daniel Fink scribbled on the wall in glitter crayon: If that is what this is for, the formula is very simple. TPM = x*42 where x is a number sufficient to justify the really cool hardware system you want. as a serious question, is TPM a valid measurement for a database? or are there other measurements that give a more valid picture of performance and/or utilization? -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Capital letters were always the best way of dealing with things you didn't have a good answer to. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: measuring TPM
I would say that it depends on the system. If we are talking about a stock trading system, then TPM is very important as is transaction-time-to-completion. For a data warehouse, this may be absolutely meaningless. Of course, does TPM describe the width of the database pipe or it's depth? In the first case, it could process 42 tx/min by doing 42 concurrent tx each a minute in duration. Or it could do 42 tx in serial each lasting 1.42 seconds (yes the math is correct (or almost)...try it yourself by dividing 60/42). One thing about TPM is that it is objective (if the size and nature of T can be defined). Unfortunately, Response Time Satisfaction is subjective and tougher to measure. Daniel Thater, William wrote: Daniel Fink scribbled on the wall in glitter crayon: If that is what this is for, the formula is very simple. TPM = x*42 where x is a number sufficient to justify the really cool hardware system you want. as a serious question, is TPM a valid measurement for a database? or are there other measurements that give a more valid picture of performance and/or utilization? -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Capital letters were always the best way of dealing with things you didn't have a good answer to. - Douglas Adams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
