Concurrency and Multiversioning - Oracle Database Transactions and Locking Revealed - page 66

■ The preceding query works regardless of the presence of any data in V$TRANSACTION . That is, even if
V$TRANSACTION is empty (because there are no transactions currently), this query returns a record. a query that has an
aggregate with no WHERE clause always returns at least one row and at most one row.

Note

In this example, that would be 8:59:30 a.m. when the transaction that modified the row started. When we go to
refresh the data at 10:00 a.m., we pull all of the changes that had occurred since that time; when we merge these into
the data warehouse, we'll have everything we need.

An Explanation for Higher Than Expected I/O on Hot Tables
Another situation where it is vital that you understand read consistency and multiversioning is when you are
faced with a query that in production, under a heavy load, uses many more I/Os than you observe in your test or
development systems, and you have no way to account for it. You review the I/O performed by the query and note that
it is much higher than you have ever seen—much higher than seems possible. You restore the production instance
on test and discover that the I/O is way down. But in production, it is still very high (but seems to vary: sometimes it
is high, sometimes it is low, and sometimes it is in the middle). The reason, as we'll see, is that in your test system, in
isolation, you do not have to undo other transactions' changes. In production, however, when you read a given block,
you might have to undo (roll back) the changes of many transactions, and each rollback could involve I/O to retrieve
the undo and apply it.
This is probably a query against a table that has many concurrent modifications taking place; you are seeing the
reads to the undo segment taking place, the work that Oracle is performing to restore the block back the way it was
when your query began. You can see the effects of this easily in a single session, just to understand what is happening.
We'll start with a very small table:

EODA@ORA12CR1> create table t ( x int );
Table created.

EODA@ORA12CR1> insert into t values ( 1 );
1 row created.

EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.

EODA@ORA12CR1> select * from t;

X
----------
1

Now we'll set our session to use the SERIALIZABLE isolation level, so that no matter how many times we run a
query in our session, the results will be “as of ” that transaction's start time:

EODA@ORA12CR1> alter session set isolation_level=serializable;
Session altered.