关于consistent read(简称CR),有网友在问这个问题，虽然看似很容易理解，然而我想不见得大家都明白，在我
查询官方文档的过程中，我也发现我以前的认识是错误的或者说理解不完整，下面是涉及到的几个概念，来自官
方文档：
session logical reads :
The sum of "db block gets" plus "consistent gets". This includes logical reads of database
blocks from either the buffer cache or process private memory.
cleanouts and rollbacks - consistent read gets:
Number of consistent gets that require both block rollbacks and block cleanouts.
cleanouts only - consistent read gets:
Number of consistent gets that require only block cleanouts, no rollbacks.
consistent gets:
Number of times a consistent read was requested for a block.
consistent changes:
Number of times a user process has applied rollback entries to perform a consistent read on the block
CR blocks created:
Number of CURRENT blocks cloned to create CR (consistent read) blocks. The most common reason for cloning
is that the buffer is held in a incompatible mode.
db block gets：
Number of times a CURRENT block was requested
关于CR read相关的几个我都列出来了，上面信息都来自官方文档，描述是非常准确的。
前面我之所以把逻辑读也列出来了，其实是因为很多朋友有一个错误的认识，例如下面这个执行计划：
SQL> alter system flush buffer_cache;
System altered.
SQL> set autot traceonly
SQL> select * from t where object_id=1000;
1 row selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 470836197
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
3 physical reads
0 redo size
1203 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
该sql执行产生的逻辑读是db block gets+consistent gets=4，不包含recursive calls的。
从上面官方文档的描述，可以看出严格意义上讲，cr read有2种情况，如下：
cleanouts and rollbacks – consistent read gets
cleanouts only – consistent read gets
简单的讲，前面一个是涉及到回滚段的，例如访问的block涉及到dml等操作，就会出现。后面一直是block不涉及到相关事务，
可能已经被写到disk上也可能事务已经，但是可能在buffer cache中，尚未写入到disk。
但是，从sql 执行计划里面反应出来的只有一种，就是onsistent gets。其实这里的onsistent gets就包括上面官方文档所
描述的3种情况：
consistent gets = cleanouts and rollbacks + cleanouts only+ consistent gets
这样描述我感觉有些不准确，consistent gets 其实细分一下我认为就是cleanouts and rollbacks和cleanouts only。
关于consistent gets 也有如下的一种描述：
This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from
the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example.
Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in
current mode to actually do the modification.
再通俗一点讲，就是为了数据一致性而产生的io请求数（io单位是block)或或必须读取的block数目。
关于cleanouts only的情况很简单，不多说了，这里用一个例子来描述下cleanouts and rollbacks的情况，如下：
---session 1
SQL> update t set owner='www.killdb.com' where object_id=100;
1 row updated.
SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
XIDUSN XIDSLOT XIDSQN UBABLK UBAFIL UBAREC
---------- ---------- ---------- ---------- ---------- ----------
1 20 859 367 2 27
SQL> select usn,name from v$rollname where usn=1;
USN NAME
---------- ------------------------------
1 _SYSSMU1$
SQL>
---session 2
SQL> conn killdb/killdb
Connected.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set events '10207 trace name context forever,level 2';
Session altered.
SQL> alter session set events '10201 trace name context forever,level 2';
Session altered.
SQL> alter session set events '10206 trace name context forever,level 2';
Session altered.
SQL> select * from t where object_id=100;
OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS
I_TYPED_VIEW1
100 100 INDEX
15-APR-10 15-APR-10 2010-04-15:13:14:45 VALID N N N
SQL> alter session set events '10201 trace name context off';
Session altered.
SQL> alter session set events '10206 trace name context off';
Session altered.
SQL> alter session set events '10207 trace name context off';
Session altered.
SQL>
——-event trace
*** 2012-08-27 19:20:56.868
*** ACTION NAME:() 2012-08-27 19:20:56.825
*** MODULE NAME:(SQL*Plus) 2012-08-27 19:20:56.825
*** SERVICE NAME:(SYS$USERS) 2012-08-27 19:20:56.825
*** SESSION ID:(138.38) 2012-08-27 19:20:56.825
Reporting status of transactions in undo segments 1
report xact xid: 0x0001.014.0000035b as not committed on scn: 0x0000.004d3923
XACT exa ret 2 on : ctx scn: 0x0000.004d3923 cr_stat scn: 0xffff.ffffffff
Finished reporting status of transactions in undo seg 1
Applying CR undo to block 4 : 1002ff5 itl entry 02:
xid: 0x0001.014.0000035b uba: 0x0080016f.03c5.1b
flg: ---- lkc: 1 fsc: 0x0000.00000000
CRS upd rd env: (scn: 0x0000.004d3923 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.00000000 8sch: scn: 0x0000.00000000) undo env: (scn: 0x0000.004d3924 xid: 0x0001.014.0000035b uba: 0x0080016f.03c5.1b
statement num=0 parent xid: xid: 0x0000.000.00000000 scn: 0x0000.20009efc 1sch: scn: 0x771c.00000036)
CRS upd (before): 0x207f151c scn: 0x0000.004d3923 xid: 0x0000.000.00000000 uba: 0x00000000.0000.00 scn: 0x0000.004d3924 sfl: 0
CRS upd (after) : 0x207f151c scn: 0x0000.004d3923 xid: 0x0001.014.0000035b uba: 0x0080016f.03c5.1b scn: 0x0000.004d3924 sfl: 0
这里针对上述trace 内容进行简单的解释：
xid: 0x0001.014.0000035b xid结构，1为回滚段、014转换为10进制是20,也就是slot号，后面的35b转换为10进制为859，是seq号。
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk# from t where object_id=100;
FILE# BLK#
---------- ----------
4 12277
SQL> alter system dump datafile 4 block 12277;
System altered.
----block dump
Block header dump: 0x01002ff5
Object id on Block? Y
seg/obj: 0xe1e5 csc: 0x00.4d3924 itc: 3 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1002ff1 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.004d312e
0x02 0x0001.014.0000035b 0x0080016f.03c5.1b ---- 1 fsc 0x0000.00000000
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0xd0d987c
===============
tsiz: 0x1f80
hsiz: 0xc0
pbl: 0x0d0d987c
bdba: 0x01002ff5
76543210
flag=--------
ntab=1
nrow=87
frre=-1
fsbo=0xc0
fseo=0x24a
avsp=0x33a
tosp=0x33a
SQL> alter system dump undo header '_SYSSMU1$';
System altered.
-----undo segmnet dump
TRN CTL:: seq: 0x03c5 chd: 0x0008 ctl: 0x001e inc: 0x00000000 nfb: 0x0000
mgc: 0x8201 xts: 0x0068 flg: 0x0001 opt: 2147483646 (0x7ffffffe)
uba: 0x0080016f.03c5.1b scn: 0x0000.004d3014
Version: 0x01
FREE BLOCK POOL::
uba: 0x00000000.03c5.1a ext: 0x2 spc: 0x1426
uba: 0x00000000.03c5.10 ext: 0x2 spc: 0x191a
uba: 0x00000000.03c5.21 ext: 0x2 spc: 0x450
uba: 0x00000000.03c5.01 ext: 0x2 spc: 0x1f88
uba: 0x00000000.0000.00 ext: 0x0 spc: 0x0
TRN TBL::
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x00 0x035a 0x0021 0x0000.004d3803 0x0080016e 0x0000.000.00000000 0x00000001 0x00000000 1346120072
......省略部分内容
0x13 9 0x00 0x035c 0x0020 0x0000.004d3638 0x00800161 0x0000.000.00000000 0x00000001 0x00000000 1346119173
0x14 10 0x80 0x035b 0x0002 0x0000.004d390c 0x0080016f 0x0000.000.00000000 0x00000001 0x00000000 0
0x15 9 0x00 0x0359 0x000c 0x0000.004d36cf 0x0080016f 0x0000.000.00000000 0x00000009 0x00000000 1346119250
比较一下下面几个scn 就明白了。oracle这里是根据比较几个scn 去决定是否进行cr read的。
undo Tx slot scn 004d390c –> 5060876
commit scn 004d3923 –> 5060899
block scn 004d3924 –> 5060900
SQL> select dbms_utility.data_block_address_file(TO_NUMBER('80016f', 'XXXXXXXX')) file_id,
2 dbms_utility.data_block_address_block(TO_NUMBER('80016f', 'XXXXXXXX')) block_id from dual;
FILE_ID BLOCK_ID
---------- ----------
2 367
SQL> alter system dump datafile 2 block 367;
System altered.
SQL>
---undo block dump
*-----------------------------
* Rec #0x1b slt: 0x14 objn: 57829(0x0000e1e5) objd: 57829 tblspc: 4(0x00000004)
* Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
uba: 0x0080016f.03c5.19 ctl max scn: 0x0000.004d2fd7 prv tx scn: 0x0000.004d3014
txn start scn: scn: 0x0000.004d390c logon user: 81
prev brb: 8388957 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03 ver: 0x01
op: Z
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x01002ff5 hdba: 0x01002ff3
itli: 2 ispac: 0 maxfr: 4858
tabn: 0 slot: 7(0x7) flag: 0x2c lock: 0 ckix: 0
ncol: 13 nnew: 1 size: -11
col 0: [ 3] 53 59 53
这里的53 59 53就是我们所查询列的前镜像值，我们通过函数转换一下看下是否是SYS。
SQL> conn roger/roger
Connected.
SQL> select hextostr('53 59 53') colname from dual;
COLNAME
-----------------------------------------------------------------
SYS
SQL>
最后还有几个概念，例如consistent changes、CR blocks created 大家自己去研究琢磨琢磨！
回复 Dong_2 取消回复