mysql 数据热点块_如何查找热点块

最新推荐文章于 2022-08-17 13:37:22 发布

努力的大头兵

最新推荐文章于 2022-08-17 13:37:22 发布

阅读量733

点赞数

文章标签： mysql 数据热点块

本文链接：https://blog.csdn.net/weixin_36180385/article/details/113596890

版权

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。如果是并行服务器环境中的索引对象，并且这个索引是系列递增类型，我们可以考虑反向索引。

分享到：

2011-12-03 00:50

努力的大头兵

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 数据热点块_如何查找热点块

V$LATCH字段说明：gets表示总共有这么多次请求，misses表示请求失败的次数(加锁不成功)，而sleeps 表示请求失败休眠的次数，通过sleeps我们可以大体知道数据库中latch的竞争是否严重，这也间接的表征了热点块的问题是否严重查看与热块有关的latch信息(cache buffer%)：SQL> SELECT latch#, NAME, gets, misses, slee...
复制链接

扫一扫