两个重要的等待事件！

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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1009078/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1009078/