RE: How to see howmany transactions commit per minute?

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Thu, 25 Jul 2002 10:48:51 -0800
Message-ID: <F001.004A2ACB.20020725104851@fatcity.com>

Before I installed STATSPACK, I had a dbms_job run this every five minutes (in fact it still runs):

	DECLARE
		v_tx_count	NUMBER;
	BEGIN

		SELECT SUM(VALUE) INTO v_tx_count
		FROM V$SYSSTAT
		WHERE NAME IN ('user commits','user rollbacks');

		INSERT INTO MY_TRANSACTION_LOG
			(TX_COUNT, TIMESTAMP)
			VALUES (v_tx_count, SYSDATE);
		COMMIT;
	END;

It runs subsecond and puts no discernable load on my system, AFAIK. To query, I wrote a l'il webpage that makes this call:

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 SYSTEM.QT_TRANSACTION_LOG
	WHERE TIMESTAMP >= TO_DATE('&start_timestamp','MM/DD/YYYY:HH24:MI')
		AND TIMESTAMP <=
TO_DATE('&end_timestamp','MM/DD/YYYY:HH24:MI')
	)

WHERE TPM IS NOT NULL
ORDER BY 1; Because of the LAG function, it's 8i-and-up-only (as an aside, I just looked at this and the final "ORDER BY" probably isn't needed because of LAG). And don't forget to divide by the number of minutes between samples to get the "PM" in "TPM".

I also dump the output into GNUPLOT for output on the webpage, but that part's optional. ;) It's great for seeing those huge spikes in the middle of the night where someone coded a COMMIT after every one of 50K INSERTS...

HTH! Enjoy! :)

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Freeman, Robert [mailto:Robert_Freeman_at_csx.com]
> Sent: Thursday, July 25, 2002 1:04 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: How to see howmany transactions commit per minute?
>
>
> Transactions committing per minute... isn't that redundant? :-)
>
> Statspack report will tell you. Also, v$sysstat will help
> you, ya just need
> to query it at time point 0 and time point 1 and diff the
> result. Each user
> commit would essentially be one transaction.
>
> RF
>
>
> Robert G. Freeman - Oracle OCP
> Oracle Database Architect
> CSX Midtier Database Administration
> Author
> Oracle9i RMAN Backup and Recovery (Oracle Press - Oct 2002)
> Oracle9i New Features (Oracle Press)
> Mastering Oracle8i (Sybex)
>
> The avalanche has begun, It is too late for the pebbles to vote.
>
>
>
> -----Original Message-----
> Sent: Thursday, July 25, 2002 1:09 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Hi Gurus,
>
> Greetings. How to see howmany transactions commit per minute?
> v$sysstat does
> not help me.
>
>
> Thanks,
>
> Jonny

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Thu Jul 25 2002 - 13:48:51 CDT