oracle常見異常等待——latch處理思路

library cache pin/lock  

     library cache lock/pin發生在多個session對相同library cache物件進行爭用發生，一般來說在物件進行DDL時候發生，如：儲存過程編譯過程中發生並堵塞編譯/表修改   解決方案：

======堵塞者和被堵塞者在同一節點


set linesize 500


col spid format a10


col username format a15 


col machine format a15 


col program format a20


col sql_fulltext format a100 


select b.spid,a.sid,a.username,a.machine,a.program,c.sql_fulltext


from v$session a,v$process b,v$sql c


where a.paddr=b.addr


and a.sql_id=c.sql_id


and a.paddr in


      (SELECT s.paddr   FROM x$kglpn p, v$session s


        WHERE p.kglpnuse = s.saddr(+)     AND p.kglpnmod <> 0     and kglpnhdl in


              (select p1raw     from v$session_wait     where event in (&#39;library cache pin&#39;, &#39;library cache lock&#39;,   &#39;library cache load lock&#39;)


              )


      );


/






======堵塞者和被堵塞者在不同一節點


1.在問題節點，檢視是什麼物件被PIN 在library cache中


set linesize 300


col owner for a20


col object for a20


SELECT KGLNAOWN owner,KGLNAOBJ object


  FROM x$kglob


  WHERE kglhdadr in(select P1RAW from v$session_wait where event like &#39;library cache%&#39;);










2、在另外節點執行（帶入上一個語句執行出的KGLNAOBJ）


set linesize 300


col sql_text for a100


select sid, serial#, sql_text from dba_kgllock w, v$session s, v$sqlarea a


where w.kgllkuse = s.saddr 


and s.sql_address = a.address


and s.sql_hash_value = a.hash_value


And w.kgllkhdl =(select kglhdadr from x$kglob where kglnaobj = &#39;PRO_YJB&#39;);

Latch:Library Cache  

     原因：為了尋找空閒Chunk,透過shared pool鎖存器，實現保護掃描空閒列和分配適當Chunk;為了執行SQL。透過library cache鎖存器,保護檢索並管理庫高速緩衝區的所有工作。在獲得library cache鎖存器過程中,若發生爭用,則等待latch:library cache事件。library cache 鎖存器爭用主要在如下情況下發生:   1、Hard Parsing 或Soft Parsing 過多時   2、Version count如果很高，也會引發這個問題，因為找到handle以後， 對child的搜尋是用遍歷的方式，如果version很高child很多，則每次搜尋的時間很可能會變長。會造成對handle上的鎖，持有的時間也變長。   3、SGA區域發生Page out時，增加session_cache_cursors是個可能的嘗試，如果cursor被cache住，child的位置會被儲存在pga中，那麼查詢child的速度會明顯加快

library cache: mutex X  

    11G以後，latch:library cache被替換為library cache: mutex X。鎖的粒度變小，每個bucket和每個handle都被一個單獨的mutex保護，大幅度減小了爭用   1、當發生硬解析時，無論要建立一個新的handle，還是要建立一個新的Child，都需要額外大量的時間來完成，相應地，對這個mutex的持有時間會明顯變長，造成爭用。由於硬解析會向shared pool申請大量的記憶體，因此這種情況會伴隨一系列其他等待如 latch: shared pool, library cache load lock。 解決辦法就是分析硬解析過多，或者version count過高的問題。   2、OS資源不足，特別是CPU資源緊張。 這種情況下，oracle程式無法獲取足夠的資源去完成相應的工作，無法及時釋放此mutex，造成請求的堆積。   3、再有，就是某個持有者長時間不釋放此mutex，造成請求的積壓。這是不正常的， 需要對mutex持有者的當前狀態進行分析。 Errorstack, process dump。 如果持有者被其他程式阻塞，則透過hanganalyze等工具繼續查詢最終持有者   4、個別Oracle bug也會造成此類問題

latch:cache buffers chains  

     原因：欲在高速緩衝區上搜尋特定塊的程式，需要獲得cache buffers chains鎖存器。在此過程中，若發生爭用，則等待latch:cache buffers chains事件。  

   1、不夠最佳化的SQL。   大量邏輯讀的SQL語句就有可能產生非常嚴重的latch:cache buffers chains等待，因為每次要訪問一個block，就需要獲得該latch，由於有大量的邏輯讀，那麼就增加了latch:cache buffers chains爭用的機率。   對於正在執行的SQL語句，產生非常嚴重的latch:cache buffers chains爭用，可以利用下面SQL檢視執行計劃，並設法最佳化SQL語句。select * from table(dbms_xplan.display_cursor('sql_id',sql_child_number));   如果SQL已經執行完畢，我們就看AWR報表裡面的SQL Statistics->SQL ordered by Gets->Gets per Exec，試圖最佳化這些SQL。  

   2、熱點塊爭用  

2.1 查詢資料庫是否存在latch的爭用select sid,event,p1text,p1raw from v$session_wait where event='latch: cache buffers chains';   


2.2 下面查詢查出Top 5 的爭用的latch address。


select * from( select CHILD#,ADDR,GETS ,MISSES,SLEEPS from v$latch_children where name = 'cache buffers chains' and misses>0 and sleeps>0 order by 5 desc, 1, 2, 3) where rownum<6;

   3、然後利用下面查詢找出Hot block。    

select /*+ RULE */       e.owner ||'.'|| e.segment_name segment_name,       e.extent_id extent#,       x.dbablk - e.block_id + 1 block#,       x.tch, /* sometimes tch=0,we need to see tim */x.tim ,l.child#     from       v$latch_children l,       x$bh x,       dba_extents e     where       x.hladdr = '&ADDR' and       e.file_id = x.file# and       x.hladdr = l.addr and       x.dbablk between e.block_id and e.block_id + e.blocks -1     order by x.tch desc ;       e.owner ||'.'|| e.segment_name segment_name,       e.extent_id extent#,       x.dbablk - e.block_id + 1 block#,       x.tch, /* sometimes tch=0,we need to see tim */x.tim ,l.child#     from       v$latch_children l,       x$bh x,       dba_extents e     where       x.hladdr = '&ADDR' and       e.file_id = x.file# and       x.hladdr = l.addr and       x.dbablk between e.block_id and e.block_id + e.blocks -1     order by x.tch desc ;

   4、Hash Bucket太少   需要更改_db_block_hash_buckets隱含引數。其實在Oracle9i之後，我們基本上不會遇到這個問題了，除非遇到Bug。所以這個是不推薦的，記住，在對Oracle的隱含引數做修改之前一定要諮詢Oracle Support。

latch：buffer cache lru chain  

      原因：高速緩衝區上與搜尋空閒緩衝區（free buffer）和髒緩衝區（dirty buffer）的程式，需要獲得cache buffers lru chain鎖存器。在此過程中，若發生爭用，則等待latch:cache buffers lru chain事件。  

     1、適當增大 Buffer Cache，這樣可以減少讀資料到 Buffer Cache 的機會，減少掃Lru List 的競爭。 

  2、可以適當增加 LRU Latch 的數量，修改_db_block_lru_latches 引數可以實現，但是該引數通常來說是足夠的，除非在 Oracle Support 的建議下或確知該引數將帶來的影響，否則不推薦修改。  

     3、透過多緩衝池技術，可以減少不希望的資料老化和全表掃等操作對於 Default池的衝擊，從而可以減少競爭。  

     4、最佳化 SQL，減少資料讀取，從而減少對於 LRU List 的掃描