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