Yuan Yao, An Oracle ACE's Blog

The number of transactions is an important metric for measuring the load on an Oracle database, e.g., in TPC-C benchmarks. A transaction typically consists of SQL statements that modify data. However, a seemingly simple question arises: Can a single select SQL statement without data modification be counted as a transaction? One might argue that Oracle implicitly starts a transaction when the first executable SQL statement is encountered. Additionally, even though a single select does not modify data, but it still adds to the workload and maintains a consist view during its execution.

To determine this, we will conduct a simple experiment.

SQL> SELECT
  2      s.name,
  3      m.value
  4  FROM
  5      V$MYSTAT m
  6  JOIN
  7      V$STATNAME s
  8  ON
  9      m.statistic# = s.statistic#
 10  WHERE
 11      s.name IN ('user commits', 'user rollbacks','execute count');
NAME               VALUE
user commits           0
user rollbacks         0
execute count          8

SQL>  SELECT * FROM dual;
DUMMY
X

SQL> SELECT
  2      s.name,
  3      m.value
  4  FROM
  5      V$MYSTAT m
  6  JOIN
  7      V$STATNAME s
  8  ON
  9      m.statistic# = s.statistic#
 10  WHERE
 11      s.name IN ('user commits', 'user rollbacks','execute count');
NAME               VALUE
user commits           0
user rollbacks         0
execute count         10

SQL> select * from v$transaction;

no rows selected

Observing the preceding experiment, we can see execution of a single select statement increments neither value of user commit nor user rollbacks , it only increments the the value of execute count . Additionally, it does not insert record to v$transaction. At this point, it seems that a single select SQL statement should not be counted as a transaction.

We take this experiment further by explicitly beginning and ending a transaction around a single select SQL statement.

SQL> SET TRANSACTION READ WRITE;

Transaction succeeded.

SQL>  SELECT * FROM dual;
DUMMY
X

SQL> select * from v$transaction;

no rows selected
SQL> commit;

Commit complete.

SQL> SELECT
  2      s.name,
  3      m.value
  4  FROM
  5      V$MYSTAT m
  6  JOIN
  7      V$STATNAME s
  8  ON
  9      m.statistic# = s.statistic#
 10  WHERE
 11      s.name IN ('user commits', 'user rollbacks','execute count');
NAME               VALUE
user commits           0
user rollbacks         0
execute count         15

The result of this experiment is the same as before.

Why is that? Let’s refer my favorite Oracle documentation Concepts:

A transaction ID is not allocated until an undo segment and transaction table slot are allocated, which occurs during the first DML statement.

Thus, the reason is that there is no data modification occurring; hence, no transaction ID is allocated, result in no transaction being created.