RE: measuring TPM

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Wed, 28 Jan 2004 11:04:36 -0800
Message-ID: <F001.005DE4B6.20040128110436@fatcity.com>

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/YYYY 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/YYYY:HH24:MI')
		AND TIMESTAMP <= TO_DATE('&2','MM/DD/YYYY: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 Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

p.s. When will the Home Depot in West Bend, WI be built? :)

-----Original Message-----
[mailto:Charlie_Mengler_at_HomeDepot.com]
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: Rich.Jesse_at_qtiworld.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). Received on Wed Jan 28 2004 - 13:04:36 CST