ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle的逻辑结构(表空间、段、区间、块)——Oracle数据块(二)

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

原创 Linux操作系统 作者:bq_wang 时间:2009-02-10 00:22:59 0 删除 编辑
Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

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


关于热点块的查询


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

 

================获取当前持有最热点数据块的Latchbuffer信息==========

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


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

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    412
  • 访问量
    1122508