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

總結

以上是生活随笔為你收集整理的0330Cache Buffers chains与共享模式疑问的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。