0821Cache Buffers chains與共享模式疑問4
[20170821]Cache Buffers chains與共享模式疑問4.txt
--//昨天別人問的問題,就是在讀讀模式下,訪問相同資料塊,11.2.0.4不再出現cache buffers chains latch等待事件.
--//我查詢我以前寫的部落格,連結如下:http://blog.itpub.net/267265/viewspace-1822491/
--//也就是oracle在這樣模式下不再採用EXCLUSIVE模式獲取cache buffers chains latch.當時的討論連結如下:
--//http://www.itpub.net/thread-1941761-2-1.html
--//隨便解答為什麼開始出現latch: shared pool,而在增加會話出現cursor: pin S的問題.
--//時間有點久,重新溫習補充一些測試,順便糾正自己以前理解上的一些錯誤.
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> select count(*) from (SELECT ADDR FROM V$LATCH_CHILDREN WHERE LOWER (name) LIKE 'cache buffers chains' ORDER BY 1) ;
COUNT(*)
----------
4096
--//我的測試環境共有4096個cache buffers chains.
SCOTT@book> select * from (SELECT ADDR FROM V$LATCH_CHILDREN WHERE LOWER (name) LIKE 'cache buffers chains' ORDER BY 1) where rownum<=10;
ADDR
----------------
0000000084436020
00000000844360E8
00000000844361B0
0000000084436278
0000000084436340
0000000084436408
00000000844364D0
0000000084436598
0000000084436660
0000000084436728
10 rows selected.
--//0x84436020=2219008032
--//0x844360E8=2219008232
--//0x844361B0=2219008432
--//可以發現上下相減就是200.
2.首先按照vage書介紹,如果透過主鍵或者唯一索引等值訪問,Oracle將採用共享CBC Latch，也就是這時多程式同時讀同一塊，將不會有CBC Latch等待。
並且僅僅獲取1次latch,而如果執行語句使用rowid訪問,要獲取2次latch.
SCOTT@book> select rowid,emp.* from emp where empno=7369;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@book> @&r/rowid AAAVREAAEAAAACXAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
87108 4 151 0 0x1000097 4,151 alter system dump datafile 4 block 151 ;
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
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000084BCF6E0 4 151 1 data block xcur 1 0 0 0 0 0 0000000074538000 EMP
SYS@book> select addr,name,gets,misses,sleeps from v$latch_children where addr=hextoraw('0000000084BCF6E0');
ADDR NAME GETS MISSES SLEEPS
---------------- -------------------- ---------- ---------- ----------
0000000084BCF6E0 cache buffers chains 421 0 0
--//gets=421.
SCOTT@book> select rowid,emp.* from emp where empno=7369;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SYS@book> select addr,name,gets,misses,sleeps from v$latch_children where addr=hextoraw('0000000084BCF6E0');
ADDR NAME GETS MISSES SLEEPS
---------------- -------------------- ---------- ---------- ----------
0000000084BCF6E0 cache buffers chains 422 0 0
--//gets=422.僅僅增加1次. 這種情況下讀取方式非常特殊,僅僅獲取1次latch.
SCOTT@book> select rowid,emp.* from emp where rowid ='AAAVREAAEAAAACXAAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SYS@book> select addr,name,gets,misses,sleeps from v$latch_children where addr=hextoraw('0000000084BCF6E0');
ADDR NAME GETS MISSES SLEEPS
---------------- -------------------- ---------- ---------- ----------
0000000084BCF6E0 cache buffers chains 424 0 0
--//gets=424.僅僅增加2次. 參考連結:http://blog.itpub.net/267265/viewspace-1453630/
邏輯讀的過程
1、Oracle以每個塊的檔案號、塊號和型別做HASH運算,得到HASH值。根據HASH值，到HASH表中取出指定塊的記憶體地址
2、獲取CBC Latch(實驗的重點測試部分)
3、根據HASH值,搜尋CBC連結串列
4、根據DBA找到BH(Buffer Header)加Buffer Pin
5、加完Buffer Pin馬上釋放CBC Latch
6、訪問Buffer開始fetch資料
7、獲取CBC Latch
8、釋放Buffer Pin
9、釋放CBC Latch
3.測試在讀讀模式下是否會出現cache buffers chains latch.
SCOTT@book> select rowid,empno,'sqlplus -s scott/book @h2 2e6 '||rowid c60 from emp ;
ROWID EMPNO C60
------------------ ---------- ------------------------------------------------------------
AAAVREAAEAAAACXAAA 7369 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAA
AAAVREAAEAAAACXAAB 7499 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAB
AAAVREAAEAAAACXAAC 7521 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAC
AAAVREAAEAAAACXAAD 7566 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAD
AAAVREAAEAAAACXAAE 7654 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAE
AAAVREAAEAAAACXAAF 7698 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAF
AAAVREAAEAAAACXAAG 7782 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAG
AAAVREAAEAAAACXAAH 7788 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAH
AAAVREAAEAAAACXAAI 7839 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAI
AAAVREAAEAAAACXAAJ 7844 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAJ
AAAVREAAEAAAACXAAK 7876 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAK
AAAVREAAEAAAACXAAL 7900 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAL
AAAVREAAEAAAACXAAM 7902 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAM
AAAVREAAEAAAACXAAN 7934 sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAN
14 rows selected.
--//建立指令碼:
$ cat bbb.sh
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAA
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAB
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAC
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAD
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAE
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAF
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAG
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAH
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAI
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAJ
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAK
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAL
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAM
sqlplus -s scott/book @h2 2e6 AAAVREAAEAAAACXAAN
$ cat h2.sql
set verify off
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
--//執行,注我沒有使用繫結變數..
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select * from emp ;
...
$ . bbb.sh
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ----- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
000000006010D810 0000000000000150 00 1611716624 336 0 94 507 74 latch: shared pool WAITED SHORT TIME 108 8
000000006010D810 0000000000000150 00 1611716624 336 0 107 399 64 latch: shared pool WAITED SHORT TIME 132 8
000000006010D810 0000000000000150 00 1611716624 336 0 119 163 65 latch: shared pool WAITED SHORT TIME 96 8
000000006010D810 0000000000000150 00 1611716624 336 0 146 33 55 latch: shared pool WAITED SHORT TIME 92 8
000000006010D810 0000000000000150 00 1611716624 336 0 248 21 59 latch: shared pool WAITED SHORT TIME 146 8
000000006010D810 0000000000000150 00 1611716624 336 0 184 23 72 latch: shared pool WAITED SHORT TIME 141 8
000000006010D810 0000000000000150 00 1611716624 336 0 197 2639 79 latch: shared pool WAITED SHORT TIME 138 8
000000006010D810 0000000000000150 00 1611716624 336 0 237 21 79 latch: shared pool WAITED SHORT TIME 115 8
000000006010D810 0000000000000150 00 1611716624 336 0 172 41 58 latch: shared pool WAITED SHORT TIME 124 8
9 rows selected.
--//注:前面可能還看到library cache: mutex X.
--//另外不要以為看到是佔用時間很長,實際上很快,如果你多次執行檢查WAIT_TIME_MICRO列資訊基本不變.
--//這是因為我沒有使用繫結變數,瞬間執行這麼多存在共享池的少量爭用. 不要被一直看到這個等待時間迷惑住.
--//wait.sql指令碼如下:
select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle'
and sid not in (select sid from v$mystat where rownum=1)
order by event ;
--//如果你在執行以上指令碼,因為執行的sql語句已經在共享池,這樣就並不會出現等待事件latch: shared pool.
SCOTT@book> @ &r/wait
no rows selected
--//再追加會話(也就是執行指令碼bbb.sh),這個時候執行的sql語句就存在爭用.這樣就會出現cursor: pin S等待事件.
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
00000000FED04CCB 000000D200000002 0000000900000000 4275064011 9.0194E+11 3.8655E+10 2 1 26 cursor: pin S WAITED KNOWN TIME 21989 1
000000005ACF2B33 0000009000000002 0000000900000000 1523526451 6.1848E+11 3.8655E+10 15 1 26 cursor: pin S WAITED KNOWN TIME 21997 1
00000000442BC338 0000008400000001 0000000900000000 1143718712 5.6694E+11 3.8655E+10 55 83 25 cursor: pin S WAITED KNOWN TIME 10520 1
00000000EF787C90 0000006B00000002 0000000900000000 4017650832 4.5956E+11 3.8655E+10 80 49 26 cursor: pin S WAITED KNOWN TIME 10323 1
000000000F1DE22E 00 0000000300000000 253616686 0 1.2885E+10 275 45 31 cursor: pin S WAITED SHORT TIME 2 0
00000000BF44A91D 0000007800000001 0000000300000000 3208947997 5.1540E+11 1.2885E+10 106 23 27 cursor: pin S WAITED KNOWN TIME 10927 1
000000008CF6D633 0000012000000002 0000000900000000 2364986931 1.2370E+12 3.8655E+10 158 17 26 cursor: pin S WAITED SHORT TIME 134 1
00000000FED04CCB 0000000200000001 0000000900000000 4275064011 8589934593 3.8655E+10 210 19 26 cursor: pin S WAITED KNOWN TIME 10715 0
00000000077D57C8 000000AB00000001 0000000300000000 125654984 7.3444E+11 1.2885E+10 261 5 28 cursor: pin S WAITED KNOWN TIME 10366 0
0000000039E57F03 00 0000000300000000 971341571 0 1.2885E+10 94 41 25 cursor: pin S WAITED SHORT TIME 4 0
10 rows selected.
--//這說明一個問題,如果程式大量執行相同的語句,就會在cursor上產生爭用,出現cursor: pin S.
--//如果改用繫結變數:
$ cat h2.sql
set verify off
declare
m_id number;
m_rowid varchar2(20);
m_data varchar2(200);
begin
m_rowid := '&2';
for i in 1 .. &&1 loop
/* select ename into m_data from emp where rowid='&&2'; */
select ename into m_data from emp where rowid =m_rowid ;
end loop;
end ;
/
quit
--//這樣全部語句就共享相同的游標,問題更加嚴重.注意看下面的P1都是85994372,這個表示sql語句的hash_value.
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
0000000005202B84 000000AC0000000A 0000000300000000 85994372 7.3873E+11 1.2885E+10 94 525 37 cursor: pin S WAITED SHORT TIME 7 3
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 119 167 43 cursor: pin S WAITED SHORT TIME 6 3
0000000005202B84 0000000000000008 0000000300000000 85994372 8 1.2885E+10 133 71 42 cursor: pin S WAITED SHORT TIME 2 1
0000000005202B84 0000000000000009 0000000900000000 85994372 9 3.8655E+10 146 35 44 cursor: pin S WAITED SHORT TIME 2 0
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 158 25 41 cursor: pin S WAITED KNOWN TIME 11008 3
0000000005202B84 0000000000000009 0000000900000000 85994372 9 3.8655E+10 172 43 53 cursor: pin S WAITED SHORT TIME 2 3
0000000005202B84 000000AC0000000A 0000000300000000 85994372 7.3873E+11 1.2885E+10 276 11 43 cursor: pin S WAITED SHORT TIME 7 3
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 197 2657 49 cursor: pin S WAITED KNOWN TIME 11008 3
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 210 237 29 cursor: pin S WAITED SHORT TIME 15 8
0000000005202B84 0000009200000008 0000000900000000 85994372 6.2707E+11 3.8655E+10 224 23 49 cursor: pin S WAITED SHORT TIME 2 1
0000000005202B84 000001050000000B 0000000900000000 85994372 1.1210E+12 3.8655E+10 237 23 47 cursor: pin S WAITED SHORT TIME 4 0
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 248 23 35 cursor: pin S WAITED SHORT TIME 9 3
0000000005202B84 0000000000000009 0000000900000000 85994372 9 3.8655E+10 261 25 43 cursor: pin S WAITED SHORT TIME 2 0
0000000005202B84 000000AC0000000A 0000000900000000 85994372 7.3873E+11 3.8655E+10 184 25 35 cursor: pin S WAITED SHORT TIME 4 3
14 rows selected.
SCOTT@book> @ &r/ev_name 'cursor: pin S'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
281 352301881 cursor: pin S idn value where 3875070507 4 Concurrency
282 1729366244 cursor: pin S wait on X idn value where 3875070507 4 Concurrency
--//idn 對應sql語句hash_values.
SCOTT@book> select sql_text,sql_id,hash_value,executions from v$sql where hash_value=85994372;
SQL_TEXT SQL_ID HASH_VALUE EXECUTIONS
------------------------------------------------------------ ------------- ---------- ----------
SELECT ENAME FROM EMP WHERE ROWID =:B1 2gvj95w2k0aw4 85994372 27249112
--//而這次看到主要等待事件是cursor: pin S,另外注意一個細節 WAIT_TIME_MICRO值是不斷變化的,說明因為執行的sql語句是使用繫結變數,在這裡存在爭用.
--//如果改用如下,這樣相當於每個會話執行的語句不能游標不能共享(註解不一樣),這樣看到的等待事件應該與第1次測試一樣.
$ cat h2.sql
set verify off
declare
m_id number;
m_rowid varchar2(20);
m_data varchar2(200);
begin
m_rowid := '&2';
for i in 1 .. &&1 loop
/* select ename into m_data from emp where rowid='&&2'; */
/* select ename into m_data from emp where rowid =m_rowid ; */
select /*+ &2 */ ename into m_data from emp where rowid =m_rowid ;
end loop;
end ;
/
quit
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ----- ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ------------------- --------------- ---------------
000000006010D810 0000000000000150 00 1611716624 336 0 81 761 28 latch: shared pool WAITED SHORT TIME 110 2
000000006010D810 0000000000000150 00 1611716624 336 0 107 413 26 latch: shared pool WAITED SHORT TIME 130 2
000000006010D810 0000000000000150 00 1611716624 336 0 248 25 26 latch: shared pool WAITED SHORT TIME 31 2
000000006010D810 0000000000000150 00 1611716624 336 0 146 37 29 latch: shared pool WAITED SHORT TIME 40 2
000000006010D810 0000000000000150 00 1611716624 336 0 237 25 25 latch: shared pool WAITED SHORT TIME 82 2
000000006010D810 0000000000000150 00 1611716624 336 0 119 169 25 latch: shared pool WAITED SHORT TIME 92 2
6 rows selected.
--//從上面三個測試,都可以發現沒有出現cache buffers chains.也就是在讀讀模式下,不會出現cache buffers chains latch的等待事件.
4.進一步深入:
--// 如果你執行上面的指令碼,在使用oradebug觀察地址0000000084BCF6E0.
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000000 00000000 17074276 000000B1 00000001 00000712 00000000 00000000 00000010 02A6DA36 00000000 00000000 00000000 00000000 00000000
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000001 00000000 17D7C2E5 000000B1 00000001 00000712 00000000 00000000 00000010 02BB22A7 00000000 00000000 00000000 00000000 00000000
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000003 00000000 189D40E2 000000B1 00000001 00000712 00000000 00000000 00000010 02DDE106 00000000 00000000 00000000 00000000 00000000
--//可以發現地址0x0000000084BCF6E0,前面4個位元組會存在一些變動.隨便提一下,後面的資訊實際上與檢視v$latch_children看到的資訊相對應.
SYS@book> select addr,name,gets,misses,sleeps from v$latch_children where addr=hextoraw('0000000084BCF6E0');
ADDR NAME GETS MISSES SLEEPS
---------------- -------------------- ---------- ---------- ----------
0000000084BCF6E0 cache buffers chains 496135311 63750503 0
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000000 00000000 1D926C8F 000000B1 00000001 00000712 00000000 00000000 00000010 03CCC167 00000000 00000000 00000000 00000000 00000000
~~~~~~~~ ~~~~~~~~
SYS@book> @ &r/10to16 496135311
10 to 16 HEX REVERSE16
-------------- ------------------
000001d926c8f 0x8f6c921d
SYS@book> @ &r/10to16 63750503
10 to 16 HEX REVERSE16
-------------- ------------------
0000003ccc167 0x67c1cc03
--//注意看下劃線內容1D926C8F.
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('84436020', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FC15B5B01F8 66 1 1 1 permanent memor 0000000084434000 3964408 perm 0 00
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('84436728', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FC15B5B0358 66 1 1 1 permanent memor 0000000084434000 3964408 perm 0 00
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('84BCF6E0', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FC15B5AE5B0 58 1 1 1 permanent memor 0000000084834000 3977832 perm 0 00
SELECT * FROM x$ksmsp WHERE TO_NUMBER ('0000000084834000', 'xxxxxxxxxxxxxxxx') between TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx') and TO_NUMBER(KSMCHPTR, 'xxxxxxxxxxxxxxxx')+KSMCHSIZ
ADDR INDX INST_ID KSMCHIDX KSMCHDUR KSMCHCOM KSMCHPTR KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
00007FC15B5B0B98 58 1 1 1 permanent memor 0000000084834000 3977832 perm 0 00
00007FC15B5B0B40 59 1 1 1 reserved stoppe 0000000084833FD0 48 R-freea 0 00
--//我不知道為什麼看到的KSMCHSIZ存在變化???? 不過這塊記憶體區域應該是perm.已經超出我的知識範圍...
5.如何看到cache buffers chains latch,自然是讀寫,寫寫模式.
--//改寫指令碼如何:(僅僅測試寫寫模式)
$ cat h2.sql
set verify off
declare
m_id number;
m_rowid varchar2(20);
m_data varchar2(200);
begin
m_rowid := '&2';
for i in 1 .. &&1 loop
/* select ename into m_data from emp where rowid='&&2'; */
/* select ename into m_data from emp where rowid =m_rowid ; */
--select /*+ &2 */ ename into m_data from emp where rowid =m_rowid ;
select /*+ &2 */ ename into m_data from emp where rowid =m_rowid for update;
end loop;
end ;
/
quit
SCOTT@book> column event format a28
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------- ------------------- --------------- ---------------
0000000000000004 0000000000000097 0000000000000001 4 151 1 81 117 39773 buffer busy waits WAITED SHORT TIME 292 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 94 63 48969 buffer busy waits WAITED SHORT TIME 299 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 107 71 50595 buffer busy waits WAITING 6 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 119 33 40399 buffer busy waits WAITED SHORT TIME 324 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 132 21 41135 buffer busy waits WAITED SHORT TIME 176 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 158 21 57854 buffer busy waits WAITING 137 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 237 19 48746 buffer busy waits WAITING 503 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 248 19 46966 buffer busy waits WAITED SHORT TIME 549 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 184 21 42532 buffer busy waits WAITED SHORT TIME 9 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 197 103 43240 buffer busy waits WAITING 380 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 210 25 44443 buffer busy waits WAITED SHORT TIME 288 0
0000000000000004 0000000000000097 0000000000000001 4 151 1 224 19 52215 buffer busy waits WAITING 186 0
0000000084BCF6E0 00000000000000B1 00 2226976480 177 0 171 21 44199 latch: cache buffers chains WAITED SHORT TIME 2 0
0000000084BCF6E0 00000000000000B1 00 2226976480 177 0 144 23 56962 latch: cache buffers chains WAITED SHORT TIME 3 0
14 rows selected.
--//可以看到 buffer busy waits,latch: cache buffers chains等待事件.
SCOTT@book> @ &r/ev_name 'buffer busy waits'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
95 2161531084 buffer busy waits file# block# class# 3875070507 4 Concurrency
--// 0x97=151 ,正好與塊號對上.
SCOTT@book> @ &r/ev_name 'latch: cache buffers chains'
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- ---------------------------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
88 2779959231 latch: cache buffers chains address number tries 3875070507 4 Concurrency
--//address =0000000084BCF6E0 ,正好就是前面的v$latch_children.addr對上.
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000029 20000000 214BC58A 000000B1 00000001 00000706 00000000 00000000 00000228 0696103B 00000434 00000000 02A04F71 00000000 001E2015
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000025 00000000 2150B8E5 000000B1 00000001 00000706 00000000 00000000 00000228 0699C54E 00000438 00000000 02A4042A 00000000 001E48B0
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000022 20000000 21560B2D 000000B1 00000001 000006D0 01000097 00000000 00000235 069DBE5A 0000043A 00000000 02A7C1FE 00000000 001E73BF
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000001 00000000 215B2FB1 000000B1 00000001 0000070F 01000097 00000000 00000239 06A195B5 0000043B 00000000 02ABA03C 00000000 001E9F12
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000000 00000000 21659548 000000B1 00000001 0000077A 00000000 00000000 00000248 06A959E2 00000448 00000000 02B323B2 00000000 001EF435
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000000 00000000 216C34BA 000000B1 00000001 000006DD 01000097 00000000 0000024A 06AE4F8E 0000044C 00000000 02B7D81C 00000000 001F29DB
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000002 00000000 21711904 000000B1 00000001 0000070A 00000000 00000000 0000024E 06B1F640 0000044D 00000000 02BB5D60 00000000 001F51F3
SYS@book> oradebug peek 0x0000000084BCF6E0 60
[084BCF6E0, 084BCF71C) = 00000023 20000000 21927DA7 000000B1 00000001 00000706 00000000 00000000 00000263 06CAEE7A 0000046F 00000000 02D3263D 00000000 00205F51
6.模式cache buffers chains latch看看?
SYS@book> oradebug poke 0x0000000084BCF6E0 8 0x0000000100000002
BEFORE: [084BCF6E0, 084BCF6E8) = 00000000 00000000
AFTER: [084BCF6E0, 084BCF6E8) = 00000002 00000001
--//8個位元組修改順便反過來.
SCOTT@book> select rowid,emp.* from emp where rowid ='AAAVREAAEAAAACXAAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
--//並沒有出現阻塞.觀察前面出現阻塞的都是0x20000000(位置0x0000000084BCF6E4).
SYS@book> oradebug poke 0x0000000084BCF6E0 8 0x2000000000000000
BEFORE: [084BCF6E0, 084BCF6E8) = 00000002 00000001
AFTER: [084BCF6E0, 084BCF6E8) = 00000000 20000000
SCOTT@book> select rowid,emp.* from emp where rowid ='AAAVREAAEAAAACXAAA';
--掛起!!
SYS@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ---------- --------------- ---------------
0000000084BCF6E0 00000000000000B1 00 2226976480 177 0 54 29 389 latch: cache buffers chains WAITING 24307480 24
SYS@book> oradebug poke 0x0000000084BCF6E0 8 0x0000000000000000
BEFORE: [084BCF6E0, 084BCF6E8) = 00000000 20000000
AFTER: [084BCF6E0, 084BCF6E8) = 00000000 00000000
SCOTT@book> select rowid,emp.* from emp where rowid ='AAAVREAAEAAAACXAAA';
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAVREAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
--//ok!正常..
7.總結:
--//在11.2.0.4下讀讀不再出現cache buffers chains latch等待事件,當然並意味著邏輯讀高的sql語句不需要最佳化,只不過估計比原來執行快一些.
--//其他東西僅僅是自己亂摸索,切記不要在生產系統做這樣的測試!!
--//另外還有一個檢視X$KSUPRLAT 觀察.select * from X$KSUPRLAT ;
SYS@book> select * from X$KSUPRLAT ;
ADDR INDX INST_ID KSUPRPID KSUPRSID KSUPRLLV KSUPRLTY KSUPRLAT KSUPRLNM KSUPRLMD KSULAWHY KSULAWHR KSULAGTS
---------------- ---- ------- -------- -------- ---------- ---------- ---------------- -------------------- ------------ -------- ---------- ----------
00007FC15B73A4C8 0 1 36 158 1 1 00000000844DFCF8 cache buffers chains MAYBE-SHARED 12584790 1778 31285956
00007FC15B73A4C8 1 1 41 224 1 0 0000000084BCF6E0 cache buffers chains EXCLUSIVE 0 1914 750666907
00007FC15B73A4C8 2 1 42 237 1 0 0000000084BCF6E0 cache buffers chains EXCLUSIVE 16777367 1807 750666911
--//不小心寫的有點長,思路有點亂.,,
--//補充測試使用的事件繫結變數與非繫結變數.
$ cat h2.sql
set verify off
column t1 format a20 new_value t1
column t2 format a20 new_value t2
select sysdate t1 from dual ;
declare
m_id number;
m_rowid varchar2(20);
m_data varchar2(200);
begin
m_rowid := '&2';
for i in 1 .. &&1 loop
select ename into m_data from emp where rowid='&&2';
/* select ename into m_data from emp where rowid =m_rowid ; */
--select /*+ &2 */ ename into m_data from emp where rowid =m_rowid ;
--select /*+ &2 */ ename into m_data from emp where rowid =m_rowid for update;
end loop;
end ;
/
select sysdate t2 from dual ;
spool /tmp/aa append
select ( to_date('&t2','yyyy-mm-dd hh24:mi:ss') - to_date('&t1','yyyy-mm-dd hh24:mi:ss'))*86400 n,'&&2' c20 from dual ;
spool off
quit
$ grep ' AAA' /tmp/aa.lst
40 AAAVREAAEAAAACXAAM
40 AAAVREAAEAAAACXAAL
41 AAAVREAAEAAAACXAAE
41 AAAVREAAEAAAACXAAN
41 AAAVREAAEAAAACXAAD
41 AAAVREAAEAAAACXAAK
41 AAAVREAAEAAAACXAAI
43 AAAVREAAEAAAACXAAH
45 AAAVREAAEAAAACXAAA
46 AAAVREAAEAAAACXAAG
47 AAAVREAAEAAAACXAAF
56 AAAVREAAEAAAACXAAC
56 AAAVREAAEAAAACXAAB
57 AAAVREAAEAAAACXAAJ
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
49 AAAVREAAEAAAACXAAN
49 AAAVREAAEAAAACXAAE
49 AAAVREAAEAAAACXAAG
50 AAAVREAAEAAAACXAAD
50 AAAVREAAEAAAACXAAF
50 AAAVREAAEAAAACXAAH
50 AAAVREAAEAAAACXAAL
53 AAAVREAAEAAAACXAAI
54 AAAVREAAEAAAACXAAC
55 AAAVREAAEAAAACXAAK
55 AAAVREAAEAAAACXAAA
56 AAAVREAAEAAAACXAAB
63 AAAVREAAEAAAACXAAM
64 AAAVREAAEAAAACXAAJ
--//分割線下面是使用繫結變數的情況,也就是呼叫select ename into m_data from emp where rowid =m_rowid ;
--//可以大量呼叫相同的sql語句,存在游標爭用,執行時間反而增加,這種情況在最佳化中一樣要注意,頻繁執行相同的sql語句.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2143880/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 1104Cache Buffers chains與共享模式疑問4AI模式
- 0330Cache Buffers chains與共享模式疑問AI模式
- 0330Cache Buffers chains與共享模式疑問2AI模式
- 1104Cache Buffers chains與共享模式疑問3AI模式
- 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
- Bug 3797171 cache buffers chains latch contention increased in 10g-3797171.8AI
- 看Chain of Responsibility模式後得疑問AI模式
- 設計模式之間互為陷阱的問題！為模式而模式的疑問！設計模式
- ValueObject和DTO模式的一些疑問Object模式