熱點塊競爭與解決

熱點塊競爭與解決

熱點塊主要是latch競爭，大量進城等待latch free競爭，我們可以從V$SESSION_WAIT檢視中查詢得到：

             SID          SEQ# EVENT

 

      ---------- ---------- -----------------------------------------------

 

                 4        14378 latch free

 

                43          1854 latch free

 

              176            977 latch free

 

              187           4393 latch free

 

              111           8715 latch free

 

              209         48534 latch free

 

如果需要具體確定熱點物件，可以從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;

 

X$BH中還存在另外一個關鍵欄位HLADDR，即hash chain latch address，這個欄位可以和v$latch_children.addr進行關聯，這樣就可以把具體的LATCH競爭和資料塊關聯起來，在結合dba_extents檢視，就可以找到具體的熱點競爭物件，找到具體熱點競爭物件之後，可以結合v$sqlarea或者v$sqltext,找到頻繁操作這些物件的SQL，然後對其進行優化，即可緩解或解決熱點塊競爭的問題。

以下SQL可以獲取當前持有最熱點資料庫的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;

利用前面提到的SQL可以找到這些熱點BUFFER的物件資訊：

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;

結合V$sqltext或v$sqlarea，可以找到操作這些物件的相關SQL，繼續查詢：

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;

找到這些SQL之後，剩下的問題就簡單了，可以通過優化SQL減少資料的訪問，避免或優化某些容易引起爭用的操作（如CONNECT BY等操作）來減少熱點塊競爭。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25964700/viewspace-703147/，如需轉載，請註明出處，否則將追究法律責任。