RE: How to see howmany transactions commit per minute?

 Hi Jesse,
That's great! I really enjoyed it ;-)) Thanks for this code and thanks who replied. Jonny
  "Jesse, Rich" <Rich.Jesse_at_qtiworld.com> wrote: 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).



---------------------------------
Get a bigger mailbox -- choose a size that fits your needs.

http://uk.docs.yahoo.com/mail_storage.html
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Jonny=20Delmont?=
  INET: jonny99delmont_at_yahoo.co.uk

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).