如何查找热点块

最新推荐文章于 2021-04-08 07:35:03 发布

Laughing

最新推荐文章于 2021-04-08 07:35:03 发布

阅读量3.2k

点赞数 1

分类专栏： Oracle 性能调整文章标签： cache sql buffer c object table

本文链接：https://blog.csdn.net/aqszhuaihuai/article/details/7036694

版权

Oracle 性能调整专栏收录该内容

20 篇文章 1 订阅

订阅专栏

V$LATCH字段说明：

gets表示总共有这么多次请求，misses表示请求失败的次数(加锁不成功)，而sleeps 表示请求失败休眠的次数，通过sleeps我们可以大体知道数据库中latch的竞争是否严重，这也间接的表征了热点块的问题是否严重

查看与热块有关的latch信息（cache buffer%）：

SQL> SELECT latch#, NAME, gets, misses, sleeps
  2    FROM v$latch
  3   WHERE NAME LIKE 'cache buffer%';
 
    LATCH# NAME                                                     GETS     MISSES     SLEEPS
---------- -------------------------------------------------- ---------- ---------- ----------
       123 cache buffer handles                                      759          0          0
       117 cache buffers lru chain                                 96611         12         12
       122 cache buffers chains                                  2253780          4          4

V$LATCH_CHILDREN包含子latch的信息，如果子latch的latch#列值相同，则说明他们有相同的父latch。

查看子latch信息（cache buffers chains）：

SQL> SELECT addr, LATCH#, CHILD#, gets, misses, sleeps
  2    FROM v$latch_children
  3   WHERE NAME = 'cache buffers chains'
  4     AND rownum < 21;
 
ADDR         LATCH#     CHILD#       GETS     MISSES     SLEEPS
-------- ---------- ---------- ---------- ---------- ----------
290C07C4        122          1       2426          0          0
290C0940        122          2        589          0          0
290C0ABC        122          3       2701          0          0
290C0C38        122          4       1844          0          0
290C0DB4        122          5       1214          0          0
290C0F30        122          6        652          0          0
290C10AC        122          7       4897          0          0
290C1228        122          8       3474          0          0
290C13A4        122          9        977          0          0
290C1520        122         10       4210          0          0
290C169C        122         11       3392          0          0
290C1818        122         12       2913          0          0
290C1994        122         13        385          0          0
290C1B10        122         14        822          0          0
290C1C8C        122         15       2333          0          0
290C1E08        122         16       4714          0          0
290C1F84        122         17       1001          0          0
290C2100        122         18        419          0          0
290C227C        122         19       1735          0          0
290C23F8        122         20       1105          0          0
 
20 rows selected

根据v$latch_child.addr关联到对应的x$bh.hladdr(这是buffer header中记录的当前buffer所处的latch地址)，通过x$bh可以获得块的文件编号和block编号。

SQL> SELECT dbarfil, dbablk
  2    FROM x$bh
  3   WHERE hladdr IN (SELECT addr
  4                      FROM (SELECT addr, LATCH#, CHILD#, gets, misses, sleeps
  5                              FROM v$latch_children
  6                             WHERE NAME = 'cache buffers chains'
  7                             ORDER BY sleeps DESC)
  8                     WHERE rownum < 11);
 
   DBARFIL     DBABLK
---------- ----------
         1      55375
         4       8500
         3        756
         3       5094
         2       1455
         1      50338
         1       2154
         3      30656
         1       6492
         3       4395
    		……
104 rows selected
 
SQL> 
SQL> SELECT dbarfil, dbablk
  2    FROM x$bh
  3   WHERE hladdr IN
  4         (SELECT addr
  5            FROM (SELECT addr FROM v$latch_children ORDER BY sleeps DESC)
  6           WHERE rownum < 11);
 
   DBARFIL     DBABLK
---------- ----------
         1      55375
         4       8500
         3        756
         3       5094
         2       1455
         1      50338
         1       2154
         3      30656
         1       6492
         3       4395
         2        769
  		……
37 rows selected

知道了文件编号和block编号，可以通过dba_extents获取相关的segment。

SQL> SELECT DISTINCT a.owner, a.segment_name, a.segment_type
  2    FROM dba_extents a,
  3         (SELECT dbarfil, dbablk
  4            FROM x$bh
  5           WHERE hladdr IN
  6                 (SELECT addr
  7                    FROM (SELECT addr, LATCH#, CHILD#, gets, misses, sleeps
  8                            FROM v$latch_children
  9                           WHERE NAME = 'cache buffers chains'
 10                           ORDER BY sleeps DESC)
 11                   WHERE rownum < 11)) b
 12   WHERE a.RELATIVE_FNO = b.dbarfil
 13     AND a.BLOCK_ID <= b.dbablk
 14     AND a.block_id + a.blocks > b.dbablk
 15     AND a.owner = 'OCP';
 
OWNER                          SEGMENT_NAME                                                                     SEGMENT_TYPE
------------------------------ -------------------------------------------------------------------------------- ------------------
OCP                            T                                                                                TABLE
SQL> SELECT DISTINCT a.owner, a.segment_name, a.segment_type
  2    FROM dba_extents a,
  3         (SELECT dbarfil, dbablk
  4            FROM x$bh
  5           WHERE hladdr IN
  6                 (SELECT addr
  7                    FROM (SELECT addr FROM v$latch_children ORDER BY sleeps DESC)
  8                   WHERE rownum < 11)) b
  9   WHERE a.RELATIVE_FNO = b.dbarfil
 10     AND a.BLOCK_ID <= b.dbablk
 11     AND a.block_id + a.blocks > b.dbablk
 12     AND a.owner = 'OCP';
 
OWNER                          SEGMENT_NAME                                                                     SEGMENT_TYPE
------------------------------ -------------------------------------------------------------------------------- ------------------
OCP                            T                                                                                TABLE
SQL>

在v$sqlarea或者v$sqltext里找到与热点块有关的sql进行优化。

其实也就是下面的语句：

SELECT sql_text
FROM v$sqltext a
WHERE a.sql_text LIKE '%t%'
ORDER BY a.hash_value, a.address, a.piece;

SELECT sql_text
  FROM v$sqltext a,
       (SELECT DISTINCT a.owner, a.segment_name, a.segment_type
          FROM dba_extents a,
               (SELECT dbarfil, dbablk
                  FROM x$bh
                 WHERE hladdr IN (SELECT addr
                                    FROM (SELECT addr,
                                                 LATCH#,
                                                 CHILD#,
                                                 gets,
                                                 misses,
                                                 sleeps
                                            FROM v$latch_children
                                           WHERE NAME = 'cache buffers chains'
                                           ORDER BY sleeps 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
           AND a.owner = 'OCP') b
 WHERE a.sql_text LIKE '%' || b.segment_name || '%'
   AND b.segment_type = 'TABLE'
 ORDER BY a.hash_value, a.address, a.piece;

SELECT sql_text
  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 a.hash_value, a.address, a.piece;

SELECT dbarfil, dbablk FROM x$bh ORDER BY tch DESC

x$bh.tch （touch count）大的block可能暗示着在当前某个周期内被访问次数比较多。

查看访问次数比较多的块所在对象：

SELECT t.owner, t.object_name, t2.dbarfil, t2.dbablk,t2.tch 
  FROM dba_objects t, x$bh t2
 WHERE t.data_object_id = t2.obj
   AND owner = 'OCP'
   AND t2.ts# > 0  --ts#表示表空间块号,是什么意思呢？
 ORDER BY t2.tch DESC

查看访问次数比较多的对象：

SELECT *
  FROM (SELECT o.owner, o.object_name, SUM(tch) TouchTime
          FROM x$bh b, dba_objects o
         WHERE b.obj = o.data_object_id AND o.owner='OCP'
         AND b.ts#>0  --ts#表示表空间块号，,是什么意思呢？
         GROUP BY o.owner, o.object_name
         ORDER BY SUM(tch) DESC)
 WHERE rownum <= 10

除了优化sql外，当然对于热点的表或者索引来说，如果小的话，我们可以考虑cache在内存中，这样可能降低物理读提高sql运行速度(这并不会减少cache buffer chains的访问次数)，对于序列，我们可以对序列多设置一些cache。如果是并行服务器环境中的索引对象，并且这个索引是系列递增类型，我们可以考虑反向索引。