0330Cache Buffers chains與共享模式疑問

[20150330]Cache Buffers chains與共享模式疑問.txt

--昨天我看了連結http://blog.itpub.net/22034023/viewspace-708296/,重複測試,無法再現,我修改一下測試方法:

1.建立測試環境:

SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

 

SCOTT@test> select rowid,empno,'sqlplus scott/btbtms @h2 2e6 '||rowid c60 from emp ;
ROWID                   EMPNO C60
------------------ ---------- ------------------------------------------------------------
AAAR3xAAEAAAACXAAA       7369 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAA
AAAR3xAAEAAAACXAAB       7499 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAB
AAAR3xAAEAAAACXAAC       7521 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAC
AAAR3xAAEAAAACXAAD       7566 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAD
AAAR3xAAEAAAACXAAE       7654 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAE
AAAR3xAAEAAAACXAAF       7698 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAF
AAAR3xAAEAAAACXAAG       7782 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAG
AAAR3xAAEAAAACXAAH       7788 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAH
AAAR3xAAEAAAACXAAI       7839 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAI
AAAR3xAAEAAAACXAAJ       7844 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAJ
AAAR3xAAEAAAACXAAK       7876 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAK
AAAR3xAAEAAAACXAAL       7900 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAL
AAAR3xAAEAAAACXAAM       7902 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAM
AAAR3xAAEAAAACXAAN       7934 sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAN
14 rows selected.

SCOTT@test> @lookup_rowid AAAR3xAAEAAAACXAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
     73201          4        151          0 4,151                alter system dump datafile 4 block 151 ;

SCOTT@test> @lookup_rowid AAAR3xAAEAAAACXAAN
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
     73201          4        151         13 4,151                alter system dump datafile 4 block 151 ;
--可以發現記錄在同一塊中.

$ 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 AAAR3xAAEAAAACXAAA    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAB    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAC    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAD    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAE    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAF    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAG    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAH    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAI    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAJ    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAK    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAL    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAM    &
sqlplus scott/btbtms @h2 2e6 AAAR3xAAEAAAACXAAN    &

SYS@test> @bh  4        151
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
00000000BCA05368          4        151          1 data block         xcur               10          0          0          0          0          0 0000000098BEE000 EMP

2.執行bbb.sh指令碼,這樣相當於開啟14個會話,訪問同一個塊的不同記錄.

SYS@test> select * from X$KSUPRLAT ;
ADDR                   INDX    INST_ID   KSUPRPID   KSUPRSID   KSUPRLLV   KSUPRLTY KSUPRLAT         KSUPRLNM              KSUPRLMD        KSULAWHY   KSULAWHR   KSULAGTS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------- ------------- ---------- ---------- ----------
0000002A975B1948          0          1         46         10          1          0 00000000BCA05368 cache buffers chains  SHARED                 0       1748    1538176

SYS@test> select * from X$KSUPRLAT ;
ADDR                   INDX    INST_ID   KSUPRPID   KSUPRSID   KSUPRLLV   KSUPRLTY KSUPRLAT         KSUPRLNM              KSUPRLMD        KSULAWHY   KSULAWHR   KSULAGTS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------- ------------- ---------- ---------- ----------
0000002A9748ABB8          0          1         47        425          1          1 00000000BCA05368 cache buffers chains  MAYBE-SHARED    16777367       1745    2265131

SYS@test> select * from X$KSUPRLAT ;
ADDR                   INDX    INST_ID   KSUPRPID   KSUPRSID   KSUPRLLV   KSUPRLTY KSUPRLAT         KSUPRLNM              KSUPRLMD        KSULAWHY   KSULAWHR   KSULAGTS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------- ------------- ---------- ---------- ----------
0000002A974B0C90          0          1         40         41          1          1 00000000BCA05368 cache buffers chains  MAYBE-SHARED           0       1748   21715950
0000002A974B0C90          1          1         46         10          1          0 00000000BCA05368 cache buffers chains  SHARED                 0       1748   21716042
0000002A974B0C90          2          1         50         27          1          1 00000000BCA05368 cache buffers chains  MAYBE-SHARED    16777367       1745   21716045

-- KSUPRLMD 並沒有出現EXCLUSIVE模式. 總之不停的執行select * from X$KSUPRLAT ;依舊無法出現EXCLUSIVE模式.

--不知道11.2.0.3又改動了什麼.也就說明如果僅僅讀資料塊,至少這個測試說明不會以EXCLUSIVE模式持有cache buffers chains.