1104Cache Buffers chains與共享模式疑問4

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

http://blog.itpub.net/267265/viewspace-1822491/

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

--做一個全表掃描看看。

SCOTT@book> select rowid,empno,'sqlplus scott/book @h3 2e6 '||ename c60 from emp ;
ROWID                   EMPNO C60
------------------ ---------- ------------------------------------------------------------
AAAVREAAEAAAACXAAA       7369 sqlplus scott/book @h3 2e6 SMITH
AAAVREAAEAAAACXAAB       7499 sqlplus scott/book @h3 2e6 ALLEN
AAAVREAAEAAAACXAAC       7521 sqlplus scott/book @h3 2e6 WARD
AAAVREAAEAAAACXAAD       7566 sqlplus scott/book @h3 2e6 JONES
AAAVREAAEAAAACXAAE       7654 sqlplus scott/book @h3 2e6 MARTIN
AAAVREAAEAAAACXAAF       7698 sqlplus scott/book @h3 2e6 BLAKE
AAAVREAAEAAAACXAAG       7782 sqlplus scott/book @h3 2e6 CLARK
AAAVREAAEAAAACXAAH       7788 sqlplus scott/book @h3 2e6 SCOTT
AAAVREAAEAAAACXAAI       7839 sqlplus scott/book @h3 2e6 KING
AAAVREAAEAAAACXAAJ       7844 sqlplus scott/book @h3 2e6 TURNER
AAAVREAAEAAAACXAAK       7876 sqlplus scott/book @h3 2e6 ADAMS
AAAVREAAEAAAACXAAL       7900 sqlplus scott/book @h3 2e6 JAMES
AAAVREAAEAAAACXAAM       7902 sqlplus scott/book @h3 2e6 FORD
AAAVREAAEAAAACXAAN       7934 sqlplus scott/book @h3 2e6 MILLER

14 rows selected.

$ cat h3.sql
declare
m_id number;
m_data varchar2(200);
begin
        for i in 1 .. &&1 loop
                select job into m_data from emp where ename='&&2';
end loop;
end ;
/
quit

$ cat bbc.sh
sqlplus scott/book @h3 2e6 SMITH &
sqlplus scott/book @h3 2e6 ALLEN &
sqlplus scott/book @h3 2e6 WARD &
sqlplus scott/book @h3 2e6 JONES &
sqlplus scott/book @h3 2e6 MARTIN &
sqlplus scott/book @h3 2e6 BLAKE &
sqlplus scott/book @h3 2e6 CLARK &
sqlplus scott/book @h3 2e6 SCOTT &
sqlplus scott/book @h3 2e6 KING &
sqlplus scott/book @h3 2e6 TURNER &
sqlplus scott/book @h3 2e6 ADAMS &
sqlplus scott/book @h3 2e6 JAMES &
sqlplus scott/book @h3 2e6 FORD &
sqlplus scott/book @h3 2e6 MILLER &

--重複測試，也一樣，也就是在11.2.0.4下，讀讀共享不會出現cache buffers chains latch。