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

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

相關文章