兩個重要的等待事件！

cache buffers chains和buffer busy waits模擬！

引用kamus說的一段話：

Cache Buffers Chains Latches用於保護記憶體中block的buffer header hash chain，任何對於處於某個hash chain中的block的操作，都要先獲得Cache Buffers Chains Latches，所以多session頻繁操作位於一個block中的資料，一定會造成Cache Buffers Chains Latches的等待，表現在v$sesssion_wait中就是一個latch free事件。

當一個程式獲得了cache buffers chains latch 之後，並且找到了自己需要操作的block的buffer header，這時候就需要獲得這個buffer header 的鎖以便於後續操作，獲得鎖之後，就會釋放掉cache buffers chains latch，而如果無法獲得（因為別的程式已經加了排斥操作的鎖），就會出現buffer busy waits等待事件。而v$session_wait檢視中的P3值可以用來確認具體是什麼原因導致了buffer busy waits等待（不過在10g中p3欄位已經變成了其它的含義了）。

[@more@]

session307:

SQL> create table t(id number , name varchar2(30));

表已建立。 T

SQL> insert into t values(1,'a');

已建立 1 行。

SQL> commit;

提交完成。

SQL> insert into t values(2,'b');

已建立 1 行。

SQL> commit;

提交完成。

SQL> select rowid , id ,name from t;

ROWID ID NAME
------------------ ---------- ------------------------------
AAAKLYAAEAAAAANAAA 1 a
AAAKLYAAEAAAAANAAB 2 b

SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_nu
mber(rowid) block# from t;

FILE# BLOCK#
---------- ----------
4 13
4 13

SQL> select sid from v$mystat where rownum=1;

SID
----------
307

SQL> declare
2 i number := 0;
3 begin
4 loop
5 update t set name='session307' where id=1;
6 i:=i+1;
7 if mod(i,100)=0 then
8 commit;
9 end if ;
10 end loop;
11 end;
12 /
^C
C:>

session3106:

session316:

SQL> select sid from v$mystat where rownum=1;

SID
----------
316

SQL> declare
2 i number := 0;
3 begin
4 loop
5 update t set name='session316' where id=2;
6 i:=i+1;
7 if mod(i,100)=0 then
8 commit;
9 end if ;
10 end loop;
11 end;
12 /
^C
C:>
C:>

session3:

SQL> select sid from v$mystat where rownum=1;

SID
----------
323

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE';

SID EVENT
---------- ----------------------------------------------------------------
308 Streams AQ: qmn slave idle wait
310 Streams AQ: waiting for time management or cleanup tasks
314 Streams AQ: qmn coordinator idle wait
320 rdbms ipc message
321 rdbms ipc message
323 SQL*Net message to client
325 rdbms ipc message
326 rdbms ipc message
327 rdbms ipc message
328 rdbms ipc message
329 smon timer

SID EVENT
---------- ----------------------------------------------------------------
330 rdbms ipc message
331 rdbms ipc message
332 rdbms ipc message
333 rdbms ipc message
334 rdbms ipc message
335 pmon timer

已選擇17行。

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
323 SQL*Net message to client

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 buffer busy waits
316 log file switch (checkpoint incomplete)
320 Log archive I/O
321 log file sequential read
323 SQL*Net message to client
331 control file sequential read

已選擇6行。

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 log file switch (checkpoint incomplete)
316 buffer busy waits
323 SQL*Net message to client

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 latch: cache buffers chains
316 buffer busy waits
320 control file sequential read
323 SQL*Net message to client
331 control file sequential read

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 buffer busy waits
316 log file switch completion
320 control file parallel write
323 SQL*Net message to client
331 control file sequential read

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 log file switch completion
316 buffer busy waits
320 control file parallel write
323 SQL*Net message to client
331 log file parallel write

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 latch: cache buffers chains
316 latch: cache buffers chains
320 control file sequential read
321 Log archive I/O
323 SQL*Net message to client
331 control file sequential read

已選擇6行。

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 buffer busy waits
316 log file switch (checkpoint incomplete)
323 SQL*Net message to client
330 enq: CF - contention
331 control file sequential read

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 buffer busy waits
316 log file switch (archiving needed)
321 Log archive I/O
323 SQL*Net message to client
331 log file single write

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
4 420 ACTIVE
5 421 CURRENT
6 419 ACTIVE

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
4 420 ACTIVE
5 421 ACTIVE
6 422 CURRENT

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
4 420 INACTIVE
5 421 ACTIVE
6 422 CURRENT

SQL> select group#,sequence#,status from v$log;

GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
4 423 ACTIVE
5 424 CURRENT
6 422 ACTIVE

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
307 buffer busy waits
316 buffer busy waits
320 Log archive I/O
321 control file sequential read
323 SQL*Net message to client
331 control file sequential read

已選擇6行。

SQL>

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
304 log file switch completion
307 buffer busy waits
316 log file switch completion
321 log file sequential read
323 SQL*Net message to client
330 control file sequential read
331 control file sequential read

已選擇7行。

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
304 log file switch (checkpoint incomplete)
307 buffer busy waits
316 log file switch (checkpoint incomplete)
320 Log archive I/O
321 control file sequential read
323 SQL*Net message to client
331 enq: CF - contention

已選擇7行。

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
304 db file sequential read
307 buffer busy waits
316 log file switch completion
320 Log archive I/O
321 control file sequential read
323 SQL*Net message to client
331 log file parallel write

已選擇7行。

SQL> select s.sid,w.event from v$session s,v$session_wait w where s.sid=w.sid an
d s.status='ACTIVE' and w.wait_class<>'Idle';

SID EVENT
---------- ----------------------------------------------------------------
304 db file scattered read
307 buffer busy waits
316 latch: cache buffers chains
320 Log archive I/O
321 control file sequential read
323 SQL*Net message to client
331 control file sequential read

已選擇7行。

這裡由於redo較小，4m，因此經常能看到有關日誌切換以及歸檔的一些等待事件的干擾，可以不去理會這幾個等待事件。我們重點關注buffer busy waits和cache buffers chains這兩個重要事件。

透過下面sql可以查詢buffer busy waits事件對應的物件。

SQL> select a.segment_name,a.segment_type from
2 (
3 select * from v$session_wait where event='buffer busy waits'
4 ) b ,
5 (
6 select * from dba_extents where owner='XYS'
7 ) a
8 where a.file_id=b.p1 and b.p2 between a.block_id and a.block_id + a.blocks
- 1
9 /

SEGMENT_NAME
--------------------------------------------------------------------------------

SEGMENT_TYPE
------------------
T
TABLE

T
TABLE

--====================================

透過下面sql可以查詢熱點塊所在的物件：

SQL> select f.segment_name,f.segment_type,e.file#,dbarfil,dbablk,tch from
2 (
3 select c.* from
4 (
5 select * from
6 (
7 select addr,hladdr,ts#,file#,dbarfil,dbablk,tch from x$bh order by tch desc

8 ) a
9 where rownum<30
10 ) c,
11 (
12 select * from
13 (
14 select * from v$latch_children
15 where name='cache buffers chains'
16 order by misses desc
17 ) b
18 where rownum<30
19 ) d
20 where c.hladdr=d.addr
21 ) e,
22 dba_extents f
23 where e.dbarfil=f.relative_fno
24 and e.dbablk between f.block_id and f.block_id + f.blocks - 1
25 /

SEGMENT_NA SEGMENT_TY FILE# DBARFIL DBABLK TCH
---------- ---------- ---------- ---------- ---------- ----------
T TABLE 6 6 11 305
RBS1 ROLLBACK 5 5 593 61
I_OBJ1 INDEX 1 1 18289 22
C_FILE#_BL CLUSTER 1 1 31460 24
OCK#

SQL>