1104Cache Buffers chains與共享模式疑問3
[20151104]Cache Buffers chains與共享模式疑問3.txt
--今天itpub上討論vage講11.2.0.4讀讀模式不會出現cache buffers chains latch，好奇做一個測試：
--連結如下：http://www.itpub.net/thread-1941761-1-1.html
1.測試環境：
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> show parameter cpu_count
NAME TYPE VALUE
---------- ------- ------
cpu_count integer 4
--為了儘快出現相關等待事件，我修改引數cpu_count=4
SCOTT@book> select rowid,empno,'sqlplus scott/book @h2 2e6 '||rowid c60 from emp ;
ROWID EMPNO C60
------------------ ----- ---------------------------------------------
AAAVREAAEAAAACXAAA 7369 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAA
AAAVREAAEAAAACXAAB 7499 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAB
AAAVREAAEAAAACXAAC 7521 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAC
AAAVREAAEAAAACXAAD 7566 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAD
AAAVREAAEAAAACXAAE 7654 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAE
AAAVREAAEAAAACXAAF 7698 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAF
AAAVREAAEAAAACXAAG 7782 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAG
AAAVREAAEAAAACXAAH 7788 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAH
AAAVREAAEAAAACXAAI 7839 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAI
AAAVREAAEAAAACXAAJ 7844 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAJ
AAAVREAAEAAAACXAAK 7876 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAK
AAAVREAAEAAAACXAAL 7900 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAL
AAAVREAAEAAAACXAAM 7902 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAM
AAAVREAAEAAAACXAAN 7934 sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAN
14 rows selected.
--看rowid可以確定這些資訊在一個資料塊中。
SCOTT@book> @ &r/rowid AAAVREAAEAAAACXAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
87108 4 151 0 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 ;
/
quit
--複製以上內容到bbb.sh,注後面加上&放在後臺執行:
$ cat bbb.sh
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAA &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAB &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAC &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAD &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAE &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAF &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAG &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAH &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAI &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAJ &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAK &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAL &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAM &
sqlplus scott/book @h2 2e6 AAAVREAAEAAAACXAAN &
SYS@book> @ &r/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
---------------- ------- ----------- ----- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000083BFD3C8 4 151 1 data block xcur 101 0 0 0 0 0 0000000078848000 EMP
--HLADDR=0000000083BFD3C8
2.執行bbb.sh指令碼,這樣相當於開啟14個會話,訪問同一個塊的不同記錄.
SYS@book> select p1raw,p2raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle' order by event ;
P1RAW P2RAW SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------- ---------- ---------- ------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 393 17 79 SQL*Net message to client WAITED SHORT TIME 0 0
000000006010D7C0 0000000000000150 592 17 31 latch: shared pool WAITED SHORT TIME 166 7
000000006010D7C0 0000000000000150 14 5 30 latch: shared pool WAITED SHORT TIME 183 7
000000006010D7C0 0000000000000150 199 21 28 latch: shared pool WAITED SHORT TIME 217 7
000000006010D7C0 0000000000000150 201 37 36 latch: shared pool WAITED SHORT TIME 134 7
000000006010D7C0 0000000000000150 202 19 36 latch: shared pool WAITED SHORT TIME 148 7
000000006010D7C0 0000000000000150 204 25 35 latch: shared pool WAITED SHORT TIME 128 7
000000006010D7C0 0000000000000150 394 37 33 latch: shared pool WAITED SHORT TIME 122 7
000000006010D7C0 0000000000000150 395 23 30 latch: shared pool WAITED SHORT TIME 142 7
000000006010D7C0 0000000000000150 396 15 41 latch: shared pool WAITED SHORT TIME 158 7
000000006010D7C0 0000000000000150 588 37 27 latch: shared pool WAITED SHORT TIME 185 7
000000006010D7C0 0000000000000150 589 29 37 latch: shared pool WAITED SHORT TIME 174 7
000000006010D7C0 0000000000000150 590 39 43 latch: shared pool WAITED SHORT TIME 205 7
000000006010D7C0 0000000000000150 9 21 34 latch: shared pool WAITED SHORT TIME 150 7
000000006010D7C0 0000000000000150 10 13 34 latch: shared pool WAITED SHORT TIME 193 7
15 rows selected.
--我有執行3個bbb.sh，確實沒有出現latch: cache buffers chains 等待事件。
SYS@book> select p1raw,p2raw,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle' order by event ;
P1RAW P2RAW SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------- ---------- ---------- ------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 393 17 87 SQL*Net message to client WAITED SHORT TIME 2 0
000000008CF6D633 000000CF00000000 602 9 48 cursor: pin S WAITED KNOWN TIME 21428 2
00000000FED04CCB 000000D500000003 13 23 113 cursor: pin S WAITED KNOWN TIME 10052 1
00000000F5C70456 0000001700000003 14 5 100 cursor: pin S WAITING 7690 0
00000000763F6E17 0000019400000003 15 25 90 cursor: pin S WAITED KNOWN TIME 17990 1
0000000039E57F03 0000019700000000 16 1 91 cursor: pin S WAITED KNOWN TIME 34634 1
000000000C0459F9 000000CA00000003 17 9 90 cursor: pin S WAITED KNOWN TIME 9974 1
00000000830A4780 000000CC00000003 19 1 80 cursor: pin S WAITED KNOWN TIME 12986 2
00000000442BC338 0000024D00000003 20 1 93 cursor: pin S WAITED KNOWN TIME 21984 0
00000000C3E7115E 0000018D00000002 21 1 66 cursor: pin S WAITED KNOWN TIME 12791 4
000000000F1DE22E 000000D100000003 22 7 59 cursor: pin S WAITED KNOWN TIME 12363 0
00000000F5C70456 0000000E00000000 23 11 46 cursor: pin S WAITED KNOWN TIME 15576 1
00000000077D57C8 0000025400000002 24 27 58 cursor: pin S WAITED KNOWN TIME 21996 1
00000000763F6E17 0000019400000003 199 21 89 cursor: pin S WAITED KNOWN TIME 10980 1
000000000F1DE22E 0000018A00000003 200 53 93 cursor: pin S WAITED SHORT TIME 9 0
00000000C3E7115E 0000018D00000003 201 37 67 cursor: pin S WAITED KNOWN TIME 19985 1
000000000C0459F9 0000001100000003 202 19 148 cursor: pin S WAITED KNOWN TIME 10996 0
00000000763F6E17 000000C700000000 203 29 83 cursor: pin S WAITED KNOWN TIME 43400 1
00000000830A4780 0000001300000003 204 25 85 cursor: pin S WAITED KNOWN TIME 11975 0
000000000C0459F9 0000025800000003 205 31 90 cursor: pin S WAITED KNOWN TIME 13673 1
000000008CF6D633 000000CF00000000 206 5 81 cursor: pin S WAITED KNOWN TIME 21434 2
000000005ACF2B33 0000019000000002 208 1 64 cursor: pin S WAITED KNOWN TIME 15993 1
000000000F1DE22E 0000018A00000003 209 1 89 cursor: pin S WAITED SHORT TIME 4018 0
00000000C3E7115E 0000018D00000003 210 27 45 cursor: pin S WAITED KNOWN TIME 21980 1
000000005ACF2B33 0000019000000002 212 11 47 cursor: pin S WAITED KNOWN TIME 12998 1
00000000FED04CCB 0000019300000000 213 13 54 cursor: pin S WAITED KNOWN TIME 11942 1
0000000039E57F03 0000001000000003 9 21 137 cursor: pin S WAITED KNOWN TIME 10992 0
00000000077D57C8 0000025400000002 10 13 110 cursor: pin S WAITED KNOWN TIME 21992 1
000000000F1DE22E 000000D100000003 394 37 140 cursor: pin S WAITED SHORT TIME 4018 0
00000000FED04CCB 000000D500000004 395 23 126 cursor: pin S WAITED KNOWN TIME 11763 1
000000008CF6D633 000000CF00000000 396 15 112 cursor: pin S WAITED KNOWN TIME 21438 2
00000000C3E7115E 000000C900000002 397 21 53 cursor: pin S WAITED KNOWN TIME 10957 2
00000000EF787C90 0000024C00000003 398 17 47 cursor: pin S WAITED KNOWN TIME 21992 0
00000000BF44A91D 0000025000000000 399 29 90 cursor: pin S WAITED KNOWN TIME 20970 1
000000005ACF2B33 000000D400000004 400 3 52 cursor: pin S WAITED KNOWN TIME 9978 2
0000000039E57F03 0000001000000003 401 13 101 cursor: pin S WAITED KNOWN TIME 7973 0
00000000F5C70456 0000001700000003 402 1 72 cursor: pin S WAITING 72 0
00000000FED04CCB 000000D500000004 403 1 87 cursor: pin S WAITED KNOWN TIME 10776 1
00000000763F6E17 000000C700000000 404 11 46 cursor: pin S WAITED KNOWN TIME 43398 1
00000000BF44A91D 0000018F00000002 405 17 51 cursor: pin S WAITED KNOWN TIME 6979 0
00000000442BC338 0000024D00000003 406 11 53 cursor: pin S WAITED KNOWN TIME 10984 0
0000000039E57F03 0000001000000003 407 7 58 cursor: pin S WAITED KNOWN TIME 16984 0
00000000EF787C90 0000025600000002 588 37 67 cursor: pin S WAITED KNOWN TIME 21991 1
00000000442BC338 0000019600000000 589 29 121 cursor: pin S WAITED KNOWN TIME 13970 1
000000005ACF2B33 0000019000000002 590 39 72 cursor: pin S WAITED KNOWN TIME 16997 1
00000000077D57C8 0000000A00000001 591 29 100 cursor: pin S WAITED KNOWN TIME 8993 0
00000000BF44A91D 0000018F00000002 592 17 117 cursor: pin S WAITED KNOWN TIME 8986 0
00000000442BC338 0000024D00000003 593 39 107 cursor: pin S WAITED KNOWN TIME 21998 0
00000000F5C70456 0000000E00000000 594 15 95 cursor: pin S WAITED KNOWN TIME 10363 1
00000000830A4780 0000001300000003 595 11 58 cursor: pin S WAITED KNOWN TIME 11995 0
00000000077D57C8 0000000A00000000 596 7 81 cursor: pin S WAITED KNOWN TIME 12994 1
00000000830A4780 0000001300000003 597 11 46 cursor: pin S WAITED KNOWN TIME 23993 0
00000000EF787C90 0000024C00000003 598 1 76 cursor: pin S WAITED KNOWN TIME 22975 0
00000000BF44A91D 0000018F00000002 599 1 86 cursor: pin S WAITED KNOWN TIME 5988 0
000000000C0459F9 0000001100000003 600 13 63 cursor: pin S WAITED KNOWN TIME 21995 0
00000000EF787C90 0000024C00000003 601 11 47 cursor: pin S WAITED KNOWN TIME 21979 0
000000006010D7C0 0000000000000150 207 1 86 latch: shared pool WAITED KNOWN TIME 10462 2
57 rows selected.
--我最終啟動14個 相當於 196個會話，依舊沒有出現cache buffers chains latch。
--檢查X$KSUPRLAT：
SYS@book> select * from X$KSUPRLAT ;
ADDR INDX INST_ID KSUPRPID KSUPRSID KSUPRLLV KSUPRLTY KSUPRLAT KSUPRLNM KSUPRLMD KSULAWHY KSULAWHR KSULAGTS
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ------------ -------- ---------- ----------
00007F8A223E9528 0 1 46 403 1 0 0000000083BFD3C8 cache buffers chains SHARED 0 1810 478952427
00007F8A223E9528 1 1 67 589 1 1 0000000083BFD3C8 cache buffers chains MAYBE-SHARED 16777367 1807 478953012
00007F8A223E9528 2 1 71 593 1 0 0000000083BFD3C8 cache buffers chains SHARED 0 1810 478953019
00007F8A223E9528 3 1 80 9 1 1 0000000083BFD3C8 cache buffers chains MAYBE-SHARED 16777367 1807 478953025
00007F8A223E9528 4 1 87 604 1 1 0000000083BFD3C8 cache buffers chains MAYBE-SHARED 16777367 1807 478953030
-- KSUPRLMD 並沒有出現EXCLUSIVE模式. 總之不停的執行select * from X$KSUPRLAT ;依舊無法出現EXCLUSIVE模式.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1822491/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 1104Cache Buffers chains與共享模式疑問4AI模式
- 0821Cache Buffers chains與共享模式疑問4AI模式
- 0330Cache Buffers chains與共享模式疑問AI模式
- 0330Cache Buffers chains與共享模式疑問2AI模式
- Cache Buffers chains,存在共享模式？AI模式
- cache buffers chains and cache buffers lru chainsAI
- latch: cache buffers chainsAI
- cache buffers chains vs cache buffers lru chainAI
- latch:cache buffers chains案例AI
- 用於排查cache buffers chainsAI
- 深入理解latch: cache buffers chainsAI
- Trouble shooting latch: cache buffers chainsAI
- ORACLE等待事件latch: cache buffers chainsOracle事件AI
- 等待事件_cache_buffers_chains_latch事件AI
- 一次latch cache buffers chains問題的處理AI
- 模擬cache buffers chains與library cache pin等待事件AI事件
- latch：cache buffers chains解決步驟AI
- latch：cache buffers chains的優化思路AI優化
- latch：cache buffers chains的最佳化思路AI
- latch: cache buffers chains---AWR實戰分析AI
- 熱點塊競爭和解決--cache buffers chainsAI
- composite模式疑問模式
- 橋模式的疑問模式
- latch: cache buffers chains故障處理總結(轉載)AI
- 解決一例latch:cache buffers chains小記AI
- buffer cache實驗6-latch:cache buffers lru chainsAI
- Chain of Responsibility模式疑問AI模式
- 工廠模式的疑問模式
- Composite模式的疑問模式
- 處理 latch_cache_buffers_chains等待事件一例AI事件
- 關於設計模式的疑問設計模式
- buffer busy waits, latch cache buffers chains, read by other session區別AISession
- 使用了索引就一定能避免cache buffers chains爭用嗎索引AI
- latch: cache buffers chains-熱塊的簡單模擬實驗AI
- Protocol Buffers 3 學習Protocol
- Bug 3797171 cache buffers chains latch contention increased in 10g-3797171.8AI
- 看Chain of Responsibility模式後得疑問AI模式
- 設計模式之間互為陷阱的問題！為模式而模式的疑問！設計模式