转载

select会访问buffer cache里哪种类型的data block,XCUR还是CR?


当我们以select一张表的时候,如果data block没有在buffer cache里,这个block就会被server process从磁盘读取到buffer cache;如果能在buffer cache里找到那么直接访问即可;v$bh.status表示了buffer cache里block的类型,常见的有以下几种
cr
free  
pi        <---RAC环境下才有
read 
scur     <---RAC环境下才有
write
xcur


在我的印象中Select操作总是与consistent这个关键字联系在一起,而DML操作总与current有关,因此select总是会访问buffer cache里cr类型的block。
其实不然:
当data block上尚有未完成的transaction时,如果新开一个session去select这个data block就会使用cr block,如果在原本的transaction里去select就会使用xcur block
当data block上没有未完成的transaction时,select这个data block就会使用xcur block


<--------x$bh里的关键字段含义-------->
x$bh.tch:block被访问的次数
x$bh.flag:block是否为脏块,同时还表示了其它一些信息
x$bh.state:block的类型


###创建测试表connect scott/773946
create table t0427_1 as select * from all_users;


select distinct dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from t0427_1;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                               22065                                    4


###数据库所使用的undo tablespace及对应的undo datafile
SQL> show parameter undo_tablespace


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      undotbs3


col name format a50
set linesize 130
select name,file# from v$datafile_header where tablespace_name='UNDOTBS3'


NAME                                                    FILE#
-------------------------------------------------- ----------
/oradata06/testaaaaa/undotbs3.dbf                          10


###这里我们开启三个session,session 1用于执行DML和select、session 2执行select、session 3用来监控
---session 3: 起初buffer cache里没有block 4/22065
SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;


no rows selected


---session 1:执行update,记录Transaction使用到的undo信息
update t0427_1 set username='XXX' where username='XDB';


select UBAFIL,UBABLK from v$transaction;
    UBAFIL     UBABLK
---------- ----------
        10      59589


---session 3:update后观察buffer cache里的data block和undo block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;
     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 xcur                2 Y
         4      22065 cr                  1 N


在update真正执行之前先把要修改的block复制出一份cr的副本
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=10 and dbablk=59589;
     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
        10      59589 xcur                2 Y
                 
---session 2:执行第一轮select
select * from t0427_1 where username='XDB';
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
XDB                                    59 20150629 16:00:05


---session 3: 第一轮后观察data block和undo block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;    
     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 cr                  1 N
         4      22065 xcur                2 Y
         4      22065 cr                  1 N
         
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=10 and dbablk=59589;    
    FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
        10      59589 xcur                3 Y


可以看到对于data block:4/22065 新增了一个cr block,对于undo block:10/59589 tch从2增加为3,表示其被访问过1次了;这里大概的作一下解释:session 2访问4/22065时发现buffer cache里已经有这个block的两个copy,一个xcur,一个cr,在我们看来最简单的方法莫过于把cr block直接拿过来使用,但是oracle并不这么认为,隐含参数_db_block_max_cr_dba默认值为6,决定了同一个data block最多能有6个类型为cr的副本,所以oracle会新生成一个block 4/22065的cr copy,我们还注意到undo block 10/59589也被访问了一次,因此生成cr copy的过程:oracle将data block 4/22065的xcur copy再复制一份出来,然后通过这份新复制出来的xcur副本头部的Transaction信息(Uba、SCN等)找到包含before-image的undo block 10/59589,进行rollback,最终形成一份崭新的cr copy


---session 2:执行第二轮select :
select * from t0427_1 where username='XDB';
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
XDB                                    59 20150629 16:00:05


---session 3: 第二轮后观察data block和undo block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;    
     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 xcur                2 Y
         4      22065 cr                  1 N
         
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=10 and dbablk=59589;    
     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
        10      59589 xcur                4 Y


第二轮执行完后,data block:4/22065 的cr copy数量从2增加为3,undo block 10/59589 tch从3增加为4


---session 2:执行第三轮select:         
select * from t0427_1 where username='XDB';
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
XDB                                    59 20150629 16:00:05


---session 3 :第三轮后观察data block和undo block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;    
     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 xcur                2 Y
         4      22065 cr                  1 N
         
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=10 and dbablk=59589;    
    FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
        10      59589 xcur                5 Y


第三轮执行完后,data block:4/22065 的cr copy数量从3增加为4,undo block 10/59589 tch从4增加为5
可见,在没有达到_db_block_max_cr_dba指定值的情况下cr copy数会持续增加


---session 1:执行select操作查询自己尚未commit的修改结果
select * from t0427_1 where username='XXX';
USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
XXX                                    59 20150629 16:00:05


---session 3 :观察data block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;    
     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 xcur                3 Y    <---增加了1
         4      22065 cr                  1 N


至此,我们发现session 1执行update时会访问xcur类型的block,在select自己没有commit的修改结果是访问的也是xcur类型的block;而当session 1没有commit的情况下,session 2自始至终select的都是CR类型的block。        


现在我们让session 1 commit
---session 1:commit之前的修改
commit;


---session 2:继续select表t0427_1
select * from t0427_1 where username='XDB';


no rows selected


---session 3:由于session 1 commit了,只观察data block
select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;    
     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 xcur                4 Y
         4      22065 cr                  1 N


因为session 1 commit了,所以4/22065这个block内容的一致性得到了保证,session 2最后的这次select访问的就是xcur类型的block;


过不久发生了incremental checkpoint


SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065


     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 xcur                4 N    <---Dirty从Y变为N
         4      22065 cr                  1 N


---session 2:再一次select表t0427_1
select * from t0427_1 where username='XDB';     


no rows selected


---session 3:这次访问的还是xcur类型对应的那个block
SQL>  select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;


     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 cr                  1 N
         4      22065 xcur                5 N
         4      22065 cr                  1 N    


---session 3: flush buffer_cache
alter system flush buffer_cache;


SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;


     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N


---session 2:select t0427_1表
select * from t0427_1 where username='XXX';


USERNAME                          USER_ID CREATED
------------------------------ ---------- -----------------
XXX                                    59 20150629 16:00:05         


SQL> select file#, dbablk, decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi', 9,'memory',10,'mwrite',11,'donated', 12,'protected',  13,'securefile', 14,'siop',15,'recckpt', 16, 'flashfree',  17, 'flashcur', 18, 'flashna') status,tch,decode(bitand(flag,1), 0, 'N', 'Y') dirty from x$bh where file#=4 and dbablk=22065;


     FILE#     DBABLK STATUS            TCH D
---------- ---------- ---------- ---------- -
         4      22065 xcur                1 N   <---仍然是xcur
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
         4      22065 free                0 N
正文到此结束
Loading...