a consistent read involves buffer gets.

A buffer get might require a physical IO (PIO), it always requires a logical IO (LIO) - by definition.

a buffer get is a LIO.


A consistent read is a read of a block "as of a point in time".

So, say this happens:

time             activity
--------         ------------
t1               open cursor for select * from one_row_table;
t2               someone updates and commits that one row
t3               someone updates and commits that one row
t4               someone updates that one row
t5               fetch from cursor

we opened the cursor at time T1. The buffers fetched for that cursor will be as of time T1. At times t2, t3, t4 - we modified that block - note that we have not read that block for that cursor opened at t1 yet, we've just opened a cursor.

Now, at time T5 we go to read the block for that one row table we might:

a) find that the block is not in the cache and do a PIO to get it into the cache
b) do the LIO to retrieve it from the cache
c) discover that it is "too new" (it has changes made at t4, it has to be as of t1)
d) we retrieve the block of UNDO that can undo this. This MIGHT incur another physical IO to read the undo back into the cache, followed by a LIO to get the undo block from the cache.
e) we roll the block back and discover it is as of time t3 - still too new
f) we repeat c-d-e
g) block is as of t2 now, we repeat c-d-e
h) block is as of t1 now - we are done, we use that copy.

so, we might have done:

1 PIO to get current version of block into cache
1 LIO to retrieve that version of the block from cache
3 PIO's to read UNDO into cache plus
3 LIO's to retrieve UNDO from cache

for a total of 4 PIO's and 4 LIO's.

the LIO's will be reported as "consistent gets" or "query mode" gets in tkprof.

On the other hand, we might (given the SAME sequence t1..t5)

a) find the block is in the cache - so no PIO needed
b) find the block is in the cache MULTIPLE times as of different points in time
c) find the block is in the cache as of the point in time we need

and do one LIO to do that consistent get.


So, we might do anywhere from 0..4 PIO's and 1..4 LIO's to do that consistent get.


In answer to your 1..4 then


1) it depends, it depends on what version of the blocks are in the cache and what is in the cache and how much work we have to do to get the blocks in the cache as of the right point in time.

2) one - a buffer get is a buffer get.

3) we can do multiblock reads so the PIO - anywhere from 1 block to our multiblock read setting.

4) see #1

consider this flow:

ops$tkyte%ORA10GR2> create table t ( x ) as select rownum from dual;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> variable y refcursor
ops$tkyte%ORA10GR2> exec open :x for select * from t x;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec open :y for select * from t y;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  update t set x = x+1;
  5                  commit;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_monitor.session_trace_enable

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

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

ops$tkyte%ORA10GR2> alter system flush buffer_cache;

System altered.

ops$tkyte%ORA10GR2> print y

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

the first print x will probably do all logical IO - getting the block back to the version it needs to be using UNDO. We'll see that as query mode gets in tkprof. It'll probably not have to roll back the changes 1,000 times - but a number a little less since Oracle keeps multiple versions of the same block in cache - you can see that:

ops$tkyte%ORA10GR2> select * from ( select file#, block#, count(*) cnt from v$bh group by file#, block# order by 3 desc ) where rownum <= 5;

     FILE#     BLOCK#        CNT
---------- ---------- ----------
         1      60984          5
         1      14719          5
         1       7824          5
         1      61038          5
         1      61116          5

there are five copies of each of those blocks in the cache right now as of different points in time.



The second print Y will have to do SOME PIO to populate the cache with the needed UNDO and the block itself - and then LIO's to roll back the changes to that block. It'll probably have to do more LIO's since only the most current version of the block will exist in the cache (the newest version)

SELECT * FROM T X

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.02          0        998          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.02          0        998          0           1
********************************************************************************
SELECT * FROM T Y

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.01         35       1003          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.01         35       1003          0           1