0330Cache Buffers chains與共享模式疑問2

lfree發表於2015-03-30

[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.

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1478834/,如需轉載,請註明出處,否則將追究法律責任。

相關文章