[20150330]Cache Buffers chains与共享模式疑问2.txt

--昨天我看了链接http://blog.itpub.net/22034023/viewspace-708296/,重复测试,无法再现,我修改一下测试方法:
--我的测试11G下,以rowid方式访问数据块,不会出现EXCLUSIVE模式持有cache buffers chains.

--10g下呢?

SYS@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@test> select rowid,empno,'sqlplus scott/btbtms @h2 2e6 '||rowid||' &' c60 from emp ;
ROWID                     EMPNO C60
------------------ ------------ ------------------------------------------------------------
AAAMlsAAEAAAAAgAAA         7369 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAA &
AAAMlsAAEAAAAAgAAB         7499 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAB &
AAAMlsAAEAAAAAgAAC         7521 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAC &
AAAMlsAAEAAAAAgAAD         7566 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAD &
AAAMlsAAEAAAAAgAAE         7654 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAE &
AAAMlsAAEAAAAAgAAF         7698 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAF &
AAAMlsAAEAAAAAgAAG         7782 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAG &
AAAMlsAAEAAAAAgAAH         7788 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAH &
AAAMlsAAEAAAAAgAAI         7839 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAI &
AAAMlsAAEAAAAAgAAJ         7844 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAJ &
AAAMlsAAEAAAAAgAAK         7876 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAK &
AAAMlsAAEAAAAAgAAL         7900 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAL &
AAAMlsAAEAAAAAgAAM         7902 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAM &
AAAMlsAAEAAAAAgAAN         7934 sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAN &
14 rows selected.

SCOTT@test> @&r/lookup_rowid AAAMlsAAEAAAAAgAAA
      OBJECT         FILE        BLOCK          ROW DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
       51564            4           32            0 4,32                 alter system dump datafile 4 block 32 ;

SCOTT@test> @&r/lookup_rowid AAAMlsAAEAAAAAgAAN
      OBJECT         FILE        BLOCK          ROW DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
       51564            4           32           13 4,32                 alter system dump datafile 4 block 32 ;

--可以发现记录在同一块中.
$ cat h2.sql
declare
m_id number;
m_data varchar2(200);
begin
        for i in 1 .. &&1 loop
                select ename into m_data from emp where rowid='&&2';
end loop;
end ;
/

----拷贝以上内容到bbb.sh:
$  cat bbb.sh
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAA  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAB  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAC  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAD  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAE  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAF  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAG  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAH  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAI  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAJ  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAK  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAL  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAM  &
sqlplus scott/btbtms @h2 2e6 AAAMlsAAEAAAAAgAAN  &

SYS@test>  @r/bh  4 32
SP2-0310: unable to open file "r/bh.sql"
SYS@test>  @ &r/bh  4 32
old  23:   dbarfil = &1 and
new  23:   dbarfil = 4 and
old  24:   dbablk = &2
new  24:   dbablk = 32

HLADDR                DBARFIL       DBABLK        CLASS CLASS_TYPE         STATE               TCH   CR_SCN_BAS   CR_SCN_WRP   CR_UBA_FIL   CR_UBA_BLK   CR_UBA_SEQ BA               OBJECT_NAME
---------------- ------------ ------------ ------------ ------------------ ---------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- --------------------
000000007B7D1B68            4           32            1 data block         xcur                 22            0            0            0            0            0 000000006BD8C000 EMP

2.执行bbb.sh脚本,这样相当于打开14个会话,访问同一个块的不同记录.

SYS@test> column KSUPRLMD format a20
SYS@test> column KSUPRLNM format a30
SYS@test> select * from X$KSUPRLAT ;
ADDR                     INDX      INST_ID     KSUPRPID     KSUPRSID     KSUPRLLV     KSUPRLTY KSUPRLAT         KSUPRLNM                       KSUPRLMD                 KSULAWHY     KSULAWHR     KSULAGTS
---------------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------------------------ -------------------- ------------ ------------ ------------
00007FE976E7F430            0            1           35          123            1            0 000000007B7D1B68 cache buffers chains           EXCLUSIVE                16777248         1275     87396800

SYS@test> select * from X$KSUPRLAT ;
ADDR                     INDX      INST_ID     KSUPRPID     KSUPRSID     KSUPRLLV     KSUPRLTY KSUPRLAT         KSUPRLNM                       KSUPRLMD                 KSULAWHY     KSULAWHR     KSULAGTS
---------------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------------------------ -------------------- ------------ ------------ ------------
00007FE976E4B128            0            1           37          121            1            1 000000007B7D1B68 cache buffers chains           EXCLUSIVE                16777248         1246     88640931

SYS@test> select * from X$KSUPRLAT ;
ADDR                     INDX      INST_ID     KSUPRPID     KSUPRSID     KSUPRLLV     KSUPRLTY KSUPRLAT         KSUPRLNM                       KSUPRLMD                 KSULAWHY     KSULAWHR     KSULAGTS
---------------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------------------------ -------------------- ------------ ------------ ------------
00007FE976E7F430            0            1           33          136            1            0 000000007B7D1B68 cache buffers chains           EXCLUSIVE                16777248         1275     89313140
00007FE976E7F430            1            1           36          147            1            1 000000007B7D1B68 cache buffers chains           EXCLUSIVE                16777248         1246     89313555

SYS@test> select * from X$KSUPRLAT ;
ADDR                     INDX      INST_ID     KSUPRPID     KSUPRSID     KSUPRLLV     KSUPRLTY KSUPRLAT         KSUPRLNM                       KSUPRLMD                 KSULAWHY     KSULAWHR     KSULAGTS
---------------- ------------ ------------ ------------ ------------ ------------ ------------ ---------------- ------------------------------ -------------------- ------------ ------------ ------------
00007FE976E4B128            0            1           17          127            1            1 000000007B7D1B68 cache buffers chains           EXCLUSIVE                16777248         1246     90064883

-- KSUPRLMD 出现EXCLUSIVE模式. 总之不停的执行select * from X$KSUPRLAT ;出现的都是EXCLUSIVE模式.
-- 看来11g下改动了什么,也就说明如果仅仅读数据块(以rowid的方式),在10g下会以EXCLUSIVE模式持有cache buffers chains.