Oracle的逻辑结构(表空间、段、区间、块)——Oracle数据块（二）

以下脚本来自于互联网，具体出处已经找不到了，如有知道还请告知！

关于热点块的查询

 

==== 查询当前数据库 最繁忙的 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;

也可以参看 热点块竞争和解决（cache buffers chains）
http://blog.oracle.com.cn/html/32/203732-4268.html