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