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

Consistent Reads and Current Reads
Oracle does do two types of block gets when processing a modification statement. It performs
• Consistent reads : When “finding” the rows to modify
• Current reads : When getting the block to actually update the row of interest
We can see this easily using TKPROF . Consider this small one row example, which reads and updates the single
row in table T from earlier:
EODA@ORA12CR1> exec dbms_monitor.session_trace_enable
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select * from t;
X
----------
10001
EODA@ORA12CR1> update t t1 set x = x+1;
1 row updated.
EODA@ORA12CR1> update t t2 set x = x+1;
1 row updated.
When we run TKPROF and view the results, we'll see something like this (note that I removed the ELAPSED , CPU ,
and DISK columns from this report):
select * from t
call count query current rows
------- ------ ------ ---------- ----------
Parse 1 0 0 0
Execute 1 0 0 0
Fetch 2 7 0 1
------- ------ ------ ---------- ----------
total 4 7 0 1
update t t1 set x = x+1
call count query current rows
------- ------ ------ ---------- ----------
Parse 1 0 0 0
Execute 1 7 3 1
Fetch 0 0 0 0
------- ------ ------ ---------- ----------
total 2 7 3 1
update t t2 set x = x+1