about consistent read gets
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: about consistent read gets
关于consistent read(简称CR),有网友在问这个问题，虽然看似很容易理解，然而我想不见得大家都明白，在我
查询官方文档的过程中，我也发现我以前的认识是错误的或者说理解不完整，下面是涉及到的几个概念，来自官
方文档：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
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相关的几个我都列出来了，上面信息都来自官方文档，描述是非常准确的。
前面我之所以把逻辑读也列出来了，其实是因为很多朋友有一个错误的认识，例如下面这个执行计划：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
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 也有如下的一种描述：
|
1 2 3 4 |
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的情况，如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
---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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
*** 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号。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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 大家自己去研究琢磨琢磨！
One Response to “about consistent read gets”
高！！！！
Leave a Reply
You must be logged in to post a comment.