Concurrency and Multiversioning - Expert Oracle Database Architecture - page 262

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.
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
...