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

Now, we'll query that small table and observe the amount of I/O performed:
EODA@ORA12CR1> set autotrace on statistics
EODA@ORA12CR1> select * from t;
X
----------
1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
...
So, that query took seven I/Os (consistent gets) in order to complete. In another session , we'll modify this table
repeatedly:
EODA@ORA12CR1> begin
2 for i in 1 .. 10000
3 loop
4 update t set x = x+1;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
And returning to our SERIALIZABLE session, we'll rerun the same query:
EODA@ORA12CR1> select * from t;
X
----------
1
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10004 consistent gets
...
It did 10,004 I/Os that time—a marked difference. So, where did all of the I/O come from? That was Oracle rolling
back the changes made to that database block. When we ran the second query, Oracle knew that all of the blocks retrieved
and processed by that query had to be “as of ” the start time of the transaction. When we got to the buffer cache, we
discovered that the block in the cache was simply “too new”—the other session had modified it some 10,000 times.
Our query could not see those changes, so it started walking the undo information and undid the last change. It
discovered this rolled back block was still too new and did another rollback of the block. It did this repeatedly until finally
it found the version of the block that was committed in the database when our transaction began. That was the block we
may use—and did use.