sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle consistent read深入详细理解

时间:2016-12-05 21:41   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于consistent read(简称CR)的深入研究笔记。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: about consistent read gets

关于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.oracleplus.net' 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$

---session 2

SQL> conn oracleplus/oracleplus
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.

——-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: 0×0001.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
     76543210Oracleо
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

最后还有几个概念,例如consistent changes、CR blocks created 大家自己去研究琢磨琢磨!

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle consistent read深入详细理解

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1368.html

Oracle研究中心

关键词:

consistent read

logical reads