buffer cache實驗7-buffer busy waits-完成

1.buffer busy waits產生原理分析：

一次邏輯讀時CBC latch鎖及Buffer pin鎖的獲取和釋放過程如下：
1.加Latch X
2.進入hash chain，在相應的BH上加Buffer pin S (0-->1)
3.釋放Latch X
4.進行邏輯讀--也就是通過BH中的buffer adderss找到資料塊在記憶體中真實位置  ---假如讀了1MS
5.加Latch X
6.釋放Buffer pin S (1-->0)  0:沒鎖  1:共享鎖 -SELECT    2:獨佔鎖-DML
7.釋放Latch X
從這個過程中可以發現和推斷：比如程式A在BH上加buffer pin 獨佔鎖時，此時CBC latch已經釋放，
在高併發環境下，此時如有程式B獲取CBC latch，在hash chain上找到BH，準備加鎖時，發現bh上已經加了獨佔鎖，就發生了buffer busy waits
要注意的是Buffer pin 上的共享鎖不會阻塞獨佔鎖，獨佔鎖會阻塞獨佔鎖--即當前讀時，再有程式來當前讀。獨佔鎖也會阻塞共享鎖--即當前讀阻塞一致性讀

在實驗中可以發現：讀程式也會阻塞寫程式，但是次數相比寫程式阻塞讀程式的，非常少。這是因為回滾段的塊爭用引起BUFFER BUSY WAITS
讀時構造CR塊需要在UNDO塊加一致讀塊，一個事務的多個DML語句可能對應同一個UNDO。讀操作使用UNDO塊構造CR塊，可能會引起BUFFER BUSY WAITS。這個可以考慮：同一事務的多個DML語句分多次提交，減少UNDO塊競爭--可以COMMIT時加個if mod(a,5000)=0;commit的判斷 每5K或者1W次提交下。
當然在BH上加buffer pin鎖和釋放是一個很快的過程-ns級吧；這裡只是說了一個理想情況下的假設。
同一事務同一回滾塊，直到回滾塊滿，再用下一個。
不同事務，使用不同回滾塊。
同一回滾塊在同一時間只能有一個事務。
10G後，有buffer busy waits與read by other session

2.buffer busy waits爭用的常見場景：

表的熱塊--多個會話併發訪問同一資料塊，
ASSM下段頭的爭用--超高併發插入時，對段頭L1塊的爭用--詳見：http://blog.csdn.net/haibusuanyun/article/details/18776657#t2
不合理PCTFREE值引起的塊空閒狀態的頻繁變化，L1塊爭用--詳見：http://blog.csdn.net/haibusuanyun/article/details/18776657#t2
MSSM管理下更容易在大併發插入時因段頭爭用產生buffer busy waits，原理和ASSM下高併發插入對L1塊爭用原理類似，只是現在用MSSM比較少，不詳說了。
undo header，可以通過增加回滾段(rollback segment)來解決緩衝區的問題。如果等待位於undo block上，我們可能需要檢查相關應用，適當減少大規模的一致性讀取，或者降低一致性讀取(consistent read)的表中的資料密度或者增大DB_CACHE_SIZE，或者是在事務的多個DML語句中增加提交的次數。---也就是OLTP中儘量用小事務可以降低buffer busy waits等待。
還有一個冷門的是：IMU模式有助減少BUFFER BUSY WAITS等待--減少從BUFFER CACEH中構建CR塊從而減少了LATCH競爭。

熱塊在導致 buffer busy waits的同時，因為併發訪問，還需要獲取CBC LATCH。可能的情況已經有會話獲取了Buffer pin鎖，此時會話1是獲取了CBC LATCH，但是無法獲得Buffer pin鎖，此時產生buffer busy waits。
如果有其它會話想要訪問此CBC LATCH下的其它hash chain或者訪問和會話1相同的BH，還會出現CBC LATCH等待。

3.read by other session等待

常見情況是：一個或多個會話要訪問buffer cache中不存在的塊，發現此資料塊正在被另一會話讀取。此時，正在讀取資料塊的會話出現：db file sequential read  / db file scattered read;
其它會話出現：read by other session

4.解決buffer busy waits爭用方法是：

臨時方法：蒐集相關資料，KILL會話，臨時解決，讓系統先恢復--不過可能正常一下很快就又不行了。
治標方法：找出涉及熱塊爭用的SQL,優化SQL，或者調整應用。

5.熱塊是普通表上的資料塊的解決方法:

1.增大PCTFREE或者使用更小的block size
2.使用小資料塊-2K-4K
3.使用HASH表分割槽
3.反向索引
熱塊是段頭的L1塊：-ASSM下段頭的爭用--超高併發插入時，對段頭L1塊的爭用--詳見：http://blog.csdn.net/haibusuanyun/article/details/18776657#t2
熱塊是undo header：可以通過增加回滾段(rollback segment)來解決緩衝區的問題。
熱塊是undo block上：我們可能需要檢查相關應用，適當減少大規模的一致性讀取，或者降低一致性讀取(consistent read)的表中的資料密度或者增大DB_CACHE_SIZE。
4.常操作的小表考慮放進KEEP POOL
5.調整隱藏引數_spin_count，增加程式成功獲取latch的可能性，這個方法要慎用，增大_spin_count會增加cpu的負荷從而可能造成負面效果

6.查詢有buffer busy waits等待的SQL語句及相應熱塊的塊號檔案號

查出最近有buffer busy waits的SQL語句：---可能通過V$ACTIVE_SESSION_HISTORY檢視進行查詢：關於此檢視詳見：http://blog.csdn.net/haibusuanyun/article/details/17959973

查出過去十分鐘內產生buffer busy waits的語句P1 資料檔案號，P2資料塊號，P3資料塊型別：  --把等待事件改一下，就可以查CBC LATCH等等了。

select SESSION_ID，event,SQL_ID,p1text,p1,p2text,p2,p3text,p3，WAIT_TIME from v$active_session_history where event like 'buffer busy waits%' and sample_time=sysdate-10/(24*60);
如果語句多且重複可能要用到GROUP BY .

--查詢30分鐘之內的最佔用時間的sqlid 與等待事件是buffer busy waits/cache buffers chains
col event for a20
select sql_id,event,count(*) from v$active_session_history
where sample_time > sysdate - 30/1440   
group by sql_id,event order by count(*) desc;

通過SQL_ID找出SQL語句：
select sql_text from v$sql where SQL_ID='';

7.關於產生buffer busy waits的實驗

見：點選開啟連結

##############################
附：還有一種查法：查詢熱塊及對應的SQL語句--因為涉及X$BH，生產環境如buffer cache大，還涉及到按TCH排序，也容易產生效能問題甚至系統HANG，不建議用）：
A、找到最熱的資料塊的latch和buffer資訊
select   b.addr,a.ts#,a.dbarfil,a.dbablk,a.tch,b.gets,b.misses,b.sleeps   from   
(select   *   from   (select   addr,ts#,file#,dbarfil,dbablk,tch,hladdr   from   x$bh   order   by   tch   desc)   where   rownum <11)   a,
(select   addr,gets,misses,sleeps   from   v$latch_children   where   name= 'cache   buffers   chains ')   b
where   a.hladdr=b.addr;

B、找到熱點buffer對應的物件資訊：
col   owner   for   a20
col   segment_name   for   a30
col   segment_type   for   a30

select   distinct   e.owner,e.segment_name,e.segment_type   from   dba_extents   e,   
(select   *   from   (select   addr,ts#,file#,dbarfil,dbablk,tch   from   x$bh   order   by   tch   desc)   where   rownum <11)   b
where   e.relative_fno=b.dbarfil
and   e.block_id <=b.dbablk
and   e.block_id+e.blocks> b.dbablk;

C、找到操作這些熱點物件的sql語句：
break   on   hash_value   skip   1
select   /*+rule*/   hash_value,sql_text   from   v$sqltext   where   (hash_value,address)   in   
(select   a.hash_value,a.address   from   v$sqltext   a,(select   distinct   a.owner,a.segment_name,a.segment_type   from   dba_extents   a,
(select   dbarfil,dbablk   from   (select   dbarfil,dbablk   from   x$bh   order   by   tch   desc)   where   rownum <11)   b   where   a.relative_fno=b.dbarfil
and   a.block_id <=b.dbablk   and   a.block_id+a.blocks> b.dbablk)   b
where   a.sql_text   like   '% '||b.segment_name|| '% '   and   b.segment_type= 'TABLE ')
order   by   hash_value,address,piece;

D.根據執行計劃,調整SQL語句。比如的表的連線方式，訪問路徑等。