oracle 一致性读模拟

--stat.sql
set lines 200
col name for a60
set pagesize 100
col value for 999,999,999,999
select s.SID, n.NAME, s.VALUE
        from v$sesstat s, v$statname n
     where s.STATISTIC# = n.STATISTIC#
	 and sid in ( select distinct sid  from v$mystat)
      and n.NAME like '%consistent%';  
	  
	  
	  
--session 1:
SQL> drop table tmp0227;

Table dropped.

SQL> create table tmp0227 as select * from dba_objects where rownum <10;

Table created.

SQL> delete tmp0227 where object_id=16;

       SID NAME                                                                    VALUE
---------- ------------------------------------------------------------ ----------------
      1164 consistent gets                                                         7,760
      1164 consistent gets from cache                                              7,760
      1164 consistent gets pin                                                     4,549
      1164 consistent gets pin (fastpath)                                          4,540
      1164 consistent gets examination                                             3,211
      1164 consistent gets examination (fastpath)                                  3,211
      1164 consistent gets direct                                                      0
      1164 fastpath consistent get quota limit                                         0
      1164 consistent changes                                                          0
      1164 transaction tables consistent reads - undo records applied                  0
      1164 transaction tables consistent read rollbacks                                0
      1164 data blocks consistent reads - undo records applied                         0
      1164 no work - consistent read gets                                          4,325
      1164 cleanouts only - consistent read gets                                      12
      1164 rollbacks only - consistent read gets                                       0
      1164 cleanouts and rollbacks - consistent read gets                              0


--session 2
col owner for a20
select owner,object_type,count(1) from  tmp0227 group by owner,object_type;  

OWNER                OBJECT_TYPE               COUNT(1)
-------------------- ----------------------- ----------
SYS                  TABLE                            4
SYS                  INDEX                            4
SYS                  CLUSTER                          1

SQL> @stat
       SID NAME                                                                    VALUE
---------- ------------------------------------------------------------ ----------------
        27 consistent gets                                                            67
        27 consistent gets from cache                                                 67
        27 consistent gets pin                                                        50
        27 consistent gets pin (fastpath)                                             50
        27 consistent gets examination                                                17
        27 consistent gets examination (fastpath)                                     17
        27 consistent gets direct                                                      0
        27 fastpath consistent get quota limit                                         0
        27 consistent changes                                                          1
        27 transaction tables consistent reads - undo records applied                  0
        27 transaction tables consistent read rollbacks                                0
        27 data blocks consistent reads - undo records applied                         1    --由于session 1 没提交，session 查询会构建一致性块，所以这里是+1
        27 no work - consistent read gets                                             42
        27 cleanouts only - consistent read gets                                       0
        27 rollbacks only - consistent read gets                                       0
        27 cleanouts and rollbacks - consistent read gets                              1
		
		
		
DECLARE
  s number ;
  BEGIN
	for i in 1 .. 1000 LOOP
	 select count(1) into s  from (select owner,object_type,count(1) from tmp0227 group by owner,object_type);
	end loop;
  end;
 /
 
SQL> @stat

       SID NAME                                                                    VALUE
---------- ------------------------------------------------------------ ----------------
        27 consistent gets                                                         4,074
        27 consistent gets from cache                                              4,074
        27 consistent gets pin                                                     2,053
        27 consistent gets pin (fastpath)                                          2,053
        27 consistent gets examination                                             2,021
        27 consistent gets examination (fastpath)                                  2,021
        27 consistent gets direct                                                      0
        27 fastpath consistent get quota limit                                         0
        27 consistent changes                                                      1,001           --值+1000
        27 transaction tables consistent reads - undo records applied                  0
        27 transaction tables consistent read rollbacks                                0
        27 data blocks consistent reads - undo records applied                     1,001            --值+1000，实际只构造了1次，读了1000次而已
        27 no work - consistent read gets                                             42
        27 cleanouts only - consistent read gets                                       1
        27 rollbacks only - consistent read gets                                       0
        27 cleanouts and rollbacks - consistent read gets                          1,001             --值+1000

16 rows selected.