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