兩個重要的等待事件！
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欄位已經變成了其它的含義了）。
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle等待事件3構造一個Direct Path write等待事件和構造一個Log File Sync等待事件Oracle事件
- Oracle 常見的33個等待事件Oracle事件
- 【等待事件】ORACLE常見等待事件事件Oracle
- 【等待事件】等待事件系列（5.1）--Enqueue(佇列等待)事件ENQ佇列
- Oracle的等待事件Oracle事件
- 等待事件事件
- Oracle中常見的33個等待事件小結Oracle事件
- 網路上的等待事件事件
- RAC中的等待事件事件
- ORACLE中的等待事件Oracle事件
- 【TUNE_ORACLE】等待事件之等待事件類別Oracle事件
- 【等待事件之二】log 相關的等待事件
- 等待事件分析事件
- oracle等待事件Oracle事件
- Oracle 等待事件Oracle事件
- px等待事件事件
- 等待事件 二事件
- 【等待事件】等待事件系列（1）--User I/O型別事件型別
- 【效能調整】等待事件(三) 常見等待事件（一）事件
- 【效能調整】等待事件(四) 常見等待事件（二）事件
- 檢視等待事件慢在哪個object上事件Object
- Oracle等待事件的種類Oracle事件
- 常見的oraclet等待事件Oracle事件
- Oracle 常見的等待事件Oracle事件
- oracle等待事件2構造一個DB File Sequential Read等待事件和構造一個Direct Path ReadOracle事件
- 與日誌有關係的幾個等待事件的解析事件
- Oracle Mutex 等待事件OracleMutex事件
- 等待事件指令碼事件指令碼
- oracle等待事件一Oracle事件
- ASH, AWR , 等待事件事件
- latch free等待事件事件
- 【Oracle概念】-等待事件Oracle事件
- 頂級等待事件：事件
- Oracle 等待事件 一Oracle事件
- 常見等待事件事件
- 【指令碼】快速排除空閒等待事件得到敏感的非空閒等待事件指令碼事件
- oracle的一些等待事件Oracle事件
- 使用兩個context實現CLOSE包的超時等待Context