热点块竞争与解决

热点块竞争与解决

热点块主要是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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25964700/viewspace-703147/