HOW MANY TRANSACTIONS?
Brian Peasland, Raytheon at EROS Data Center
INTRODUCTION
In my work with various newsgroups and the Quest Pipelines
([Link] I’ve been asked how to
determine how many transactions have occurred in the database
since instance startup. On the surface, this seems to be an easy
question to answer. But I’ve found that this question often leads to
some debate. Hopefully, this paper will shed some light on the subject
and clear up a few misunderstandings.
This paper does not go into great detail about transactions. It is
assumed that the reader does have some knowledge on the subject.
For more information on transactions, refer to the Oracle
documentation and any college-level textbooks that describe
database transaction concepts.
WHAT IS A TRANSACTION?
Before we can begin to determine how many transactions have
occurred in the database, we need to understand what a transaction
is. A transaction is one logical piece of work in the database, or “the
execution of a program that includes database access operations”.1
Some transactions are read-only transactions while other transactions
update, or write, to the database. All transactions must exhibit the
ACID properties. Transactions that make changes to the database are
completed when they are committed (made permanent) or rolled back
(undone).
The following query is an example of a read-only transaction:
ORA9I SQL> select count(*) as num_employees from emp;
NUM_EMPLOYEES
-------------
14
1
Fundamentals Of Database Systems, Elmasri and Navathe, The
Benjamin/Cummings Publishing Company, Redwood City, CA.
This simple transaction queries the database for some information.
This transaction ended as soon as the data was returned to the
application. Since no data was modified in this transaction, a commit
or rollback was unnecessary. A read-only transaction such as the SQL
statement above is a single transaction unless the user issues the SET
TRANSACTION command or wraps the read-only statements in a PL/SQL
block.
The following query updates data in the database:
ORA9I SQL> delete from emp where empno=7934;
1 row deleted.
ORA9I SQL> commit;
Commit complete.
In the last example, we made a change to the database and
committed that change. On successful commit, the transaction is
complete and a new one begins.
In some cases, one transaction can consist of multiple SQL
statements.
ORA9I SQL> update emp set ename='JOHNSON' where empno=7876;
1 row updated.
ORA9I SQL> update emp set ename='JACKSON' where empno=7902;
1 row updated.
ORA9I SQL> delete from emp where empno=7900;
1 row deleted.
ORA9I SQL> rollback;
Rollback complete.
In this example, we issued three different SQL statements before
rolling back the transaction. All three SQL statements in this example
comprised one transaction. The transaction ended with the ROLLBACK
statement. These are not three separate transactions.
COUNTING TRANSACTIONS
So you now know what a transaction is. How do we find out how many
transactions have occurred since instance startup? Luckily for us,
Oracle keeps track of this as a statistic (somewhat). These statistics
are reset when the instance is brought down. So if you want to keep
track of your transactions, you’ll have to capture this information
before the database is brought down.
The V$SYSSTAT and V$SESSTAT dynamic performance views let us get to
this information. The V$SYSSTAT view gives us “database-wide”
statistics. The V$SESSTAT gives us statistics for each current session. As
soon as the current session ends, the statistics are removed from this
view. Also, V$SESSTAT does not show the statistic name. You’ll have to
join this view with V$STATNAME on the STATISTIC# column to get the
statistic name. V$SYSSTAT does have the statistic name.
In our examples, we will be examining the V$SYSSTAT table. In our
examples, I am the only user signed on to the database so changes to
V$SYSSTAT will reflect changes made by this one user. This is very
important to see how various statistics will help us count the number
of transactions. I’m also using V$SYSSTAT so that we can easily see the
transaction name.
COUNTING COMMITS AND ROLLBACKS
Oracle has made it very easy for us to count the number of commits in
the database. Let’s take a look at an example. We are going to give a
3% raise to employee number 7369.
ORA9I SQL> select name,value from v$sysstat where name='user commits';
NAME VALUE
-------------------- ----------
user commits 16
ORA9I SQL> update emp set sal=sal*1.03 where empno=7369;
1 row updated.
ORA9I SQL> commit;
Commit complete.
ORA9I SQL> select name,value from v$sysstat where name='user commits';
NAME VALUE
-------------------- ----------
user commits 17
Notice in the code above that we looked for the ’user commits’ statistic
before and after our UPDATE statement. As can be expected, this
statistic was increased by one after we committed our work.
Similarly, there is a statistic for measuring rollbacks.
ORA9I SQL> select name,value from v$sysstat where name='user rollbacks';
NAME VALUE
-------------------- ----------
user rollbacks 4
ORA9I SQL> update emp set sal=sal*1.05 where empno=7902;
1 row updated.
ORA9I SQL> rollback;
Rollback complete.
ORA9I SQL> select name,value from v$sysstat where name='user rollbacks';
NAME VALUE
-------------------- ----------
user rollbacks 5
The ’user rollbacks’ statistic shows how many rollbacks have been
issued.
Well, that was simple. I now know how to determine rollbacks and
commits. So shouldn’t the total number of transactions be the sum of
these two numbers? This is where the debate often comes in. If you
examine the code that STATSPACK uses, you will find that STATSPACK
computes the number of transactions to be the sum of user commits
and user rollbacks. As we will see, this definition of a transaction does
not take into account the read-only transactions, or SELECT statements.
What happens with read-only transactions? In Oracle, these do not
require a commit or rollback. And we can see from the following
example that the rollback and commit statistics are not updated with
a simple query.
ORA9I SQL> select name,value from v$sysstat
2 where name in ('user rollbacks','user commits');
NAME VALUE
-------------------- ----------
user commits 17
user rollbacks 5
ORA9I SQL> select count(*) as num_emps from emp;
NUM_EMPS
----------
13
ORA9I SQL> select name,value from v$sysstat
2 where name in ('user rollbacks','user commits');
NAME VALUE
-------------------- ----------
user commits 17
user rollbacks 5
You should notice that the value of these two statistics did not change
after my SELECT statement. A SELECT statement by itself does not
update the number of commits or rollbacks.
So what difference does this make? The answer is that it all depends.
On some Online Transaction Processing (OLTP) systems, most of the
work in the database is making changes to very few records at a time.
And there are lots of changes. The extra SELECT queries don’t add that
much work to the database. So the commit and rollback statistics can
give an accurate picture of how much work is being done in the
database. If these statistic values increase, more work is being done.
But what about data warehouses or Decision Support Systems (DSS)?
Data in these systems is typically changed on a regular, maybe
infrequent, basis. For instance, let’s assume that the data is loaded
from the OLTP system into the DSS once a month. But the real work of
the DSS is the queries performed against that data. Very few changes
are made to DSS databases, as these systems are ‘query-mostly’, or
‘read-mostly’. So these two statistics are not a very good indicator of
the amount of work being done. I’ve also worked on hybrid databases
(somewhere between OLTP and DSS) that were query-mostly and
didn’t change too much. In these cases, counting just user commits
and rollbacks is not a good indicator of how many “transactions” have
occurred in the database.
COUNTING SELECTS
So how do we count SELECT statements? There is another statistic,
’user calls’, which is as close as we are going to get. Let’s see an
example of this and then we’ll discuss it.
ORA9I SQL> select name,value from v$sysstat where name='user calls';
NAME VALUE
-------------------- ----------
user calls 3945
ORA9I SQL> select count(*) as num_emps from emp;
NUM_EMPS
----------
13
ORA9I SQL> select name,value from v$sysstat where name='user calls';
NAME VALUE
-------------------- ----------
user calls 3951
We examined the total number of user calls, issued a query, and then
examine the total number of user calls once again. Notice that the
value increased by six. This is very important. To understand why this
is the case, we need to refer the Oracle documentation for this
statistic.
The Oracle documentation defines ’user calls’ as “the number of user
calls such as login, parse, fetch, or execute.” If you look at a trace of
any SELECT statement, you will see that it is parsed, executed, and
then fetched. Using the documentation definition, a SELECT statement
performs three user calls. In my example above, I started with a total
of 3,945 user calls. I issued a SELECT statement against the EMP table.
And then I issued a SELECT statement against the V$SYSSTAT view. That
is two SELECT statements. Since each SELECT statement is three user
calls each, I have a total of six user calls. That explains the difference!
Note: There are times where SQL statements do not undergo all three
(parse, fetch and execute) steps. Therefore, it is possible that the
total number of user calls in the database is not an integer multiple
of three. And DML statements do not always use three user calls as well.
‘USER CALLS’ AND COMMITS AND ROLLBACKS
Is the ’user calls’ statistic influenced by commits and rollbacks? Let’s
look at an example. We’ll see what our statistics are at the start, issue
an UPDATE command, commit the work, and then check the statistics
again.
ORA9I SQL> select name,value from v$sysstat
2 where name in ('user calls','user commits','user rollbacks');
NAME VALUE
-------------------- ----------
user commits 18
user rollbacks 5
user calls 4043
ORA9I SQL> update emp set sal=sal*1.05 where empno=7902;
1 row updated.
ORA9I SQL> commit;
Commit complete.
ORA9I SQL> select name,value from v$sysstat
2 where name in ('user calls','user commits','user rollbacks');
NAME VALUE
-------------------- ----------
user commits 19
user rollbacks 5
user calls 4051
Notice that the number of commits increase by one (as we expected)
and the number of user calls increased by eight. Our last SELECT
statement contributed three user calls to the increase in this statistic.
And since we are the only user on this system, the UPDATE and COMMIT
statements helped to contribute the other five user calls. If you look at
a trace file for the SQL statements, you will see that any DML
statements contribute some user calls. You will also see that a COMMIT
statement has a PARSE and EXECUTE phase, so it contributes to the
number of user calls. Similarly, a ROLLBACK influences the number of
user calls as well.
Since ’user calls’ is influenced by SELECT and DML statements, we can
use this as an indicator of how busy the system is. Unfortunately, we
can’t say that the total number of user calls is the total number of
database transactions. And there isn’t a statistic like ’user
transactions’. So ’user calls’ is about as close as we’re going to get.
SO WHERE DOES THIS LEAD?
So far, we’ve seen that ’user commits’ and ’user rollbacks’ gives us a
good indication of the number of commits and rollbacks taken place in
the system. But these statistics tend to give us a false indicator of how
busy the system is since they don’t capture SELECT statements. The
’user calls’ statistic is much better since it captures SELECT statements
and other DML statements. Unfortunately, there is not a one-to-one
correspondence between user calls and user statements.
So how do we keep track of the number transactions in the database?
This is where the biggest debate occurs. Hopefully, the previous
information in this paper has given you enough to go on.
If your database experiences a high volume of changes, then you may
wish to use the total number of commits and rollbacks as your
transaction indicator. The following query will give you an average
number of commits and rollbacks per day.
ORA9I SQL> select value/up_days as tx_per_day
2 from (select sum(value) as value from v$sysstat
3 where name in ('user commits','user rollbacks')),
4 (select sysdate-startup_time as up_days from v$instance);
TX_PER_DAY
----------
12.0645584
So for my instance, I’m experiencing an average of 12 commits and
rollbacks per day. This isn’t very high since this database is a single-
user, test database.
If you database does not have a high number of DML statements, then
the above query will not give you a true indicator of how busy the
system is. The following query will be a better guide.
ORA9I SQL> select value/up_days as calls_per_day
2 from (select value from v$sysstat where name='user calls'),
3 (select sysdate-startup_time as up_days from v$instance);
CALLS_PER_DAY
-------------
1495.72984
Here, on the same database, I have almost 1,500 user calls per day.
While this is not a true transaction rate, graphing this number over
time will tell you if you are generating higher or lower transaction
volumes over time. And this statistic captures SELECT and DML
statements. The previous query only captured DML statements.
I also like to divide my statistic values by the total uptime. This gives a
rate per day rather than a flat value. This is important when
comparing two systems, or two statistics, from instances that have
been up two different lengths of time.
CONCLUSION
After reading this paper, it should be obvious that it is very easy and
accurate to obtain the total number of DML statement since instance
startup by using the ’user commits’ and ’user rollbacks’ statistics. But
as we’ve shown here, these statistics do not keep track of read-only
transactions. So using those statistics to keep track of the total
number of transactions can be very misleading, especially in read-
mostly environments.
We’ve also seen how the ’user calls’ statistic does keep track of
SELECT and other DML statements, but not on a one-to-one ratio. A
typical SELECT statement will generate three user calls; one for the
PARSE phase, one for the EXECUTE phase, and one for the FETCH phase.
While I use this statistic as an indicator of how busy the system has
been for a period of time, it does not provide a true measure of the
total number of transactions performed on the system.
Which do you use? That’s probably where the biggest debate comes
in. And I suspect that this debate will continue in the Oracle
community for some time. Just use the information presented in this
paper to make the best decision for your situation. For most of my
situations, I use the total number of user calls since instance startup.