latch: cache buffers chains故障處理總結(轉載)

一大早就接到開發商的電話，說資料庫的CPU使用率為100%，應用相應遲緩。急匆匆的趕到現場發現進行了基本的檢查後發現是latch: cache buffers chains 作祟，處理過程還算順利，當時忘了記錄log，這裡總結下處理思路，以便下次檢視。

故障分析思路 

檢視等待事件，判斷故障起因

SQL>select * from (select sid,event,p1,p2,p3,p1text,WAIT_TIME,SECONDS_IN_WAIT from v$session_wait where wait_class# <> 6
order by wait_time desc) where rownum <=10;

確認為latch: cache buffers chains引起的故障後，檢視latch的命中率

SQL>SELECT name, gets, misses, sleeps, 
      immediate_gets, immediate_misses
     FROM v$latch
   WHERE name = 'cache buffers chains';

 

各列名稱意義如下

 

NAME：latch名稱
IMMEDIATE_GETS：以Immediate模式latch請求數
IMMEDIATE_MISSES：請求失敗數
GETS：以Willing to wait請求模式latch的請求數
MISSES：初次嘗試請求不成功次數
SPIN_GETS：第一次嘗試失敗，但在以後的輪次中成功
SLEEP[x]：成功獲取前sleeping次數
WAIT_TIME：花費在等待latch的時間

這裡需要注意MISSES/GETS如果在達10%左右，則說明有比較嚴重的latch爭用,也可以透過查詢v$latch_children檢視檢視其他latch資訊 ，語句如下

SQL> SELECT *
    FROM (SELECT   addr, child#, gets, misses, sleeps, immediate_gets igets,
                  immediate_misses imiss, spin_gets sgets
              FROM v$latch_children
             WHERE NAME = 'cache buffers chains'
          ORDER BY sleeps DESC)
   WHERE ROWNUM < 11;

關於latch的統計資訊，主要關注以下幾部分

misses/gets的比率是多少

獲自spinning的misses的百分比是多少

latch請求了多少次

latch休眠了多少次

檢視熱點物件和訪問資訊，TCH列表示物件被訪問的次數

SQL> SELECT *
    FROM (  SELECT addr,
                   ts#,
                   file#,
                   dbarfil,
                   dbablk,
                   tch
              FROM x$bh
          ORDER BY tch DESC)
   WHERE ROWNUM < 11; 

透過物件的檔案號和塊號檢視具體物件資訊

SQL>select owner, segment_name, partition_name, tablespace_name 
from dba_extents
where relative_fno = &v_dba_rfile
and &v_dba_block between block_id and block_id + blocks - 1;

也可以透過如下sql查詢熱點塊，主要

SELECT *

  FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME

          FROM X$BH B, DBA_OBJECTS O

         WHERE B.OBJ = O.DATA_OBJECT_ID

           AND B.TS# > 0

         GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE

         ORDER BY SUM(TCH) DESC)

 WHERE ROWNUM <= 10;

檢視引起latch: cache buffers chains的sql

SQL> select * from (select 
    count(*), 
    sql_id, 
    nvl(o.object_name,ash.current_obj#) objn,
    substr(o.object_type,0,10) otype,
     3    4    5    6        CURRENT_FILE# fn,
         CURRENT_BLOCK# blockn
   from  v$active_session_history ash
       , all_objects o
   where event like 'latch: cache buffers chains'
     and o.object_id (+)= ash.CURRENT_OBJ#
   group by sql_id, current_obj#, current_file#,
                  current_block#, o.object_name,o.object_type
   order by  count(*) desc )where rownum <=10;

根據上面得到的sql_id資訊檢視sql全文

SQL>select sql_fulltext from v$sqlarea where sql_id='&sqlid';

檢視SQL的執行計劃
SQL>SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(('&sql_id',0));

在認為sql執行計劃不準確的情況也可以透過sql_id檢視sql的address和hash_value檢視sql的實際執行計劃

SQL>SELECT  address, hash_value FROM v$sql 
     WHERE sql_id='&sql_id'; 
SQL>SELECT operation, options, object_name, cost FROM v$sql_plan 
     WHERE address = '&addr' AND hash_value = 'hash_v';

當某個會話長時間持有latch時，可以透過聯合v$latchholder和v$session檢視檢視sql資訊

SQL>SELECT s.sql_hash_value,s.sql_id,s.address, l.name
  FROM V$SESSION s, V$LATCHHOLDER l
WHERE s.sid = l.sid;

故障處理思路

1、根據sql執行計劃判斷該執行計劃是否正確，sql執行過長往往意味著過長時間的持有latch。

2、最佳化nested loop join，如果有可能使用hash join代替nested loop join。也可以利用對熱塊索引進行hash分割槽，或者使用hash簇的方式減緩熱塊現象。

3、調整表的pctfree值，將資料儘可能的分佈到多個塊中

4、調整應用

關於熱塊，可以參閱筆者的如下文章

http://czmmiao.iteye.com/blog/1474472

轉：http://czmmiao.iteye.com/blog/1766442