Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Best way to calc transactions for the month

Re: Best way to calc transactions for the month

From: <>
Date: Wed, 11 Apr 2007 13:16:43 -0500
Message-ID: <>


SELECTs really aren't "transactions" in the commonly-used sense of the word, as changes are not being made. Transactions generally involve changes (i.e. INSERT, UPDATE, or DELETE).

The sheer number of changes to the database, whether through DDL commands (i.e. CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, etc) or DML commands (i.e. INSERT, UPDATE, DELETE) can be counted in the statistic "user commits" in the V$SYSSTAT view, which is also recorded in the STATSPACK table STATS$SYSSTAT. This statistic only tracks actual committed changes, not the number of times a user session calls the "commit" command, so it is a good (though imprecisely defined) measure of "work" performed by the database.

If the president wants a good number to track over time, measuring "throughput" through the database, you could do far worse than "user commits", as long as he doesn't attempt to tie that number back directly to some other measure (i.e. number of widgets produced, number of read I/Os, etc). If you are using STATSPACK, I have a script named "sptrends.sql" at that can query STATS$SYSSTAT for a particular statistic, and display the values over time. It can also dump the data into ".csv" format for upload into MS-Excel, for graphing, etc.

This sounds like a fairly micro-managing manager, to me. :-) What is the question he is really trying to ask and then answer?

Hope this helps!


Quoting Sandra Becker <>:

> The president of the company would like to know how many transactions per
> month are running through the database. He wants to include all selects,
> inserts, updates, and deletes. What would be the best way to gather this
> information. Is there a way I can easily break it down by each type of
> transaction?
> Oracle, RHEL 4.0
> Sandy

Received on Wed Apr 11 2007 - 13:16:43 CDT

Original text of this message