[转]some scripts of hot blocks

====查询当前数据库最繁忙的Buffer，TCH(Touch)表示访问次数越高，热点快竞争问题就存在=====
SELECT *
  FROM (SELECT   addr, ts#, file#, dbarfil, dbablk, tch
            FROM x$bh
        ORDER BY tch DESC)
 WHERE ROWNUM < 11;

====查询当前数据库最繁忙的Buffer，结合dba_extents查询得到这些热点Buffer来自哪些对象=====
SELECT 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;

=============如果在Top 5中发现latch free热点块事件时，可以从V$latch_children中查询具体的子Latch信息============
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和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;

================结合SQL视图可以找到操作这些对象的相关SQL，然后通过优化SQL减少数据的访问，
或者优化某些容易引起争用的操作（如connect by等操作）来减少热点块竞争=================

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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14730395/viewspace-680558/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14730395/viewspace-680558/