前往小程序，Get更优阅读体验！
立即前往
文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
首页
学习
活动
专区
工具
TVP最新优惠活动
返回腾讯云官网
社区首页 >专栏 >实战经验：如何定位控制文件热点块，即读取延迟高的块所在的ASM磁盘

实战经验：如何定位控制文件热点块，即读取延迟高的块所在的ASM磁盘

作者头像
数据和云
发布于 2021-03-26 16:16:59
6070
发布于 2021-03-26 16:16:59
举报
文章被收录于专栏：数据和云

墨墨导读：某客户检查表空间使用率的SQL成了TOP SQL，经判断主要为control file sequential read延迟增加导致。这里不讨论怎么降低控制文件读，重点记录一下怎么定位控制文件热点块或者说读取延迟高的块所在的ASM磁盘。

从AWR中看到control file sequential read为TOP EVENT,占了89%的DB TIME.对比之前的Waits，并没有明显增加，但延迟从us级别增加到ms级别，增长明显。

iostat 定位到sdad为热点盘，延迟明显。

热点盘对应用asm disk名为asmdskemc15。

代码语言：javascript
复制
sdad                  65:208  0    2T  0 disk  
└─asmdskemc15        253:14   0    2T  0 mpath

从ASH统计control file sequential read主要慢在40，42两个block，推测control file sequential read读取的块在热点盘上。

知识点

1、某些x的信息来自控制文件，每次读取要执行oracle内核中的代码，读取控制文件。

2、一些x$是控制文件中的内容，控制文件读取后并不会缓存，每次调用都会产生物理读下面连续两次查询xkccfn，可以看到控制文件相应的块重复产生物理读。

下面连续两次查询x$kccfn，可以看到控制文件相应的块重复产生物理读。

代码语言：javascript
复制
select * from x$kccfn;

oracle@perf-monitor ~$cat /home/app/oracle/diag/rdbms/perfcdb/perfcdb/trace/perfcdb_ora_1841.trc|grep "control file sequential read"
WAIT #140285876517504: nam='control file sequential read' ela= 14 file#=0 block#=1 blocks=1 obj#=-1 tim=67389713130731  
WAIT #140285876517504: nam='control file sequential read' ela= 4 file#=0 block#=15 blocks=1 obj#=-1 tim=67389713130755
WAIT #140285876517504: nam='control file sequential read' ela= 4 file#=0 block#=17 blocks=1 obj#=-1 tim=67389713130769
WAIT #140285876517504: nam='control file sequential read' ela= 5 file#=0 block#=90 blocks=1 obj#=-1 tim=67389713130785
WAIT #140285876517504: nam='control file sequential read' ela= 5 file#=0 block#=92 blocks=1 obj#=-1 tim=67389713131972

WAIT #140285876517504: nam='control file sequential read' ela= 12 file#=0 block#=1 blocks=1 obj#=-1 tim=67389714364091
WAIT #140285876517504: nam='control file sequential read' ela= 6 file#=0 block#=15 blocks=1 obj#=-1 tim=67389714364128
WAIT #140285876517504: nam='control file sequential read' ela= 5 file#=0 block#=17 blocks=1 obj#=-1 tim=67389714364152
WAIT #140285876517504: nam='control file sequential read' ela= 7 file#=0 block#=90 blocks=1 obj#=-1 tim=67389714364177
WAIT #140285876517504: nam='control file sequential read' ela= 7 file#=0 block#=92 blocks=1 obj#=-1 tim=67389714365201

下面验证该2个block是否在热点盘上。

注意control file为细粒度条带。

借用官方文档上的两张图说明Fine-Grained Striping， Coarse-Grained Striping条带的区别。

Oracle ASM Fine-Grained Striping

Oracle ASM Coarse-Grained Striping

查看ontrol file asm file number。

代码语言：javascript
复制
SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
+DATADG/HBODS/CONTROLFILE/current.257.1017306195
SQL> select group_number,name from v$asm_diskgroup;
GROUP_NUMBER NAME
------------ ------------------------------
           1 CRSDG
           2 DATADG
           3 DATADG2

确定AU大小。

代码语言：javascript
复制
grid@hbods1:/home/grid$ kfed read /dev/mapper/asmdskemc14

kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                       0 ; 0x004: blk=0
kfbh.block.obj:              2147483668 ; 0x008: disk=20
kfbh.check:                  3074063759 ; 0x00c: 0xb73a7d8f
kfbh.fcn.base:                 20675314 ; 0x010: 0x013b7af2
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8
kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000
kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
kfdhdb.compat:                318767104 ; 0x020: 0x13000000
kfdhdb.dsknum:                       20 ; 0x024: 0x0014
kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNAL
kfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBER
kfdhdb.dskname:             DATADG_0020 ; 0x028: length=11
kfdhdb.grpname:                  DATADG ; 0x048: length=6
kfdhdb.fgname:              DATADG_0020 ; 0x068: length=11
。。。。。。
kfdhdb.secsize:                     512 ; 0x0b8: 0x0200
kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000
kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000  <<<au size

查看控制文件每个AU对应的磁盘，这里借用了之前dd抽取asm文件的一个角本。

代码语言：javascript
复制
SQL> set lines 1000

SQL> select INCARN_KFFXP,XNUM_KFFXP,LXN_KFFXP,DISK_KFFXP,AU_KFFXP,'dd if='||b.path||' of=/tmp/file'||NUMBER_KFFXP||'.dbf'||' conv=notrunc bs=1048576 skip='||AU_KFFXP||' seek='||XNUM_KFFXP||' count=1' from x$kffxp a,
  2  v$asm_disk b  wHere inst_id=1 and GROUP_KFFXP=2 AND NUMBER_KFFXP=257 
  3  and a.GROUP_KFFXP=b.GROUP_NUMBER and a.DISK_KFFXP=b.DISK_NUMBER and LXN_KFFXP=0 order by XNUM_KFFXP;
order by XNUM_KFFXP,LXN_KFFXP
INCARN_KFFXP XNUM_KFFXP  LXN_KFFXP DISK_KFFXP   AU_KFFXP 'DDIF='||B.PATH||'OF=/TMP/FILE'||NUMBER_KFFXP||'.DBF'||'CONV=NOTRUNCBS=1048576SKIP='||AU_KFFXP||'SEEK='||XNUM_KFFXP||'COUNT=1'
------------ ---------- ---------- ---------- ---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1017306195          0          0         20          5 dd if=/dev/mapper/asmdskemc14 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=5 seek=0 count=1
  1017306195          1          0         22          5 dd if=/dev/mapper/asmdskemc16 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=5 seek=1 count=1
  1017306195          2          0         23          6 dd if=/dev/mapper/asmdskemc17 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=2 count=1
  1017306195          3          0         14          6 dd if=/dev/mapper/asmdskemc5 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=3 count=1
  1017306195          4          0         18          6 dd if=/dev/mapper/asmdskemc12 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=4 count=1
  1017306195          5          0         21          6 dd if=/dev/mapper/asmdskemc15 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=5 count=1 <<<

  1017306195          6          0         11          6 dd if=/dev/mapper/asmdskemc2 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=6 count=1
  1017306195          7          0         17          6 dd if=/dev/mapper/asmdskemc11 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=7 count=1
  1017306195          8          0         12          6 dd if=/dev/mapper/asmdskemc3 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=8 count=1
  1017306195          9          0         19          6 dd if=/dev/mapper/asmdskemc13 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=9 count=1
  1017306195         10          0         13          6 dd if=/dev/mapper/asmdskemc4 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=10 count=1
  1017306195         11          0         10          7 dd if=/dev/mapper/asmdskemc1 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=11 count=1
  1017306195         12          0         24          6 dd if=/dev/mapper/asmdskemc18 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=12 count=1
  1017306195         13          0         15          6 dd if=/dev/mapper/asmdskemc9 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=13 count=1

  1017306195         14          0         20          6 dd if=/dev/mapper/asmdskemc14 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=14 count=1
  1017306195         15          0         16          6 dd if=/dev/mapper/asmdskemc10 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=15 count=1
  1017306195         16          0         22          6 dd if=/dev/mapper/asmdskemc16 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=16 count=1
  1017306195         17          0         23          7 dd if=/dev/mapper/asmdskemc17 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=17 count=1
  1017306195         18          0         14          7 dd if=/dev/mapper/asmdskemc5 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=18 count=1
  1017306195         19          0         18          7 dd if=/dev/mapper/asmdskemc12 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=19 count=1
  1017306195         20          0         21          7 dd if=/dev/mapper/asmdskemc15 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=20 count=1
  1017306195         21          0         11          7 dd if=/dev/mapper/asmdskemc2 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=21 count=1
  1017306195         22          0         17          7 dd if=/dev/mapper/asmdskemc11 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=22 count=1
  1017306195         23          0         12          7 dd if=/dev/mapper/asmdskemc3 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=23 count=1
  1017306195         24          0         19          7 dd if=/dev/mapper/asmdskemc13 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=24 count=1
  1017306195         25          0         13          7 dd if=/dev/mapper/asmdskemc4 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=25 count=1
  1017306195         26          0         10         12 dd if=/dev/mapper/asmdskemc1 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=12 seek=26 count=1
  1017306195         27          0         24          7 dd if=/dev/mapper/asmdskemc18 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=27 count=1
  1017306195         28          0         15          7 dd if=/dev/mapper/asmdskemc9 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=28 count=1
  1017306195         29          0         20          7 dd if=/dev/mapper/asmdskemc14 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=29 count=1
  1017306195         30          0         16          7 dd if=/dev/mapper/asmdskemc10 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=30 count=1
  1017306195         31          0         22          7 dd if=/dev/mapper/asmdskemc16 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=7 seek=31 count=1
32 rows selected

确定控制文件条带粒度，精细条带的大小，条带的宽度，用来计算BLOCK 40，42在哪个AU的哪个位置上。

隐含参数_asm_stripesize 代表了精细条带的大小， 默认为 128K， 隐含参数，_asm_stripewidth 代表了条带的宽度， 默认为 8。

代码语言：javascript
复制
SQL> select * from v$asm_file where FILE_NUMBER=257 and GROUP_NUMBER=2;

GROUP_NUMBER FILE_NUMBER COMPOUND_INDEX INCARNATION BLOCK_SIZE     BLOCKS      BYTES      SPACE TYPE                                                             REDUND STRIPE CREATION_DATE     MODIFICATION_DATE R PERMISSIONS      USER_NUMBER USER_INCARNATION USERGROUP_NUMBER USERGROUP_INCARNATION PRIM MIRR  HOT_READS HOT_WRITES HOT_BYTES_READ HOT_BYTES_WRITTEN COLD_READS COLD_WRITES COLD_BYTES_READ COLD_BYTES_WRITTEN FILEGROUP_NUMBER FILEGROUP_INCARNATION R PARENT_FILNUM PARENT_FILNUMINC
------------ ----------- -------------- ----------- ---------- ---------- ---------- ---------- ---------------------------------------------------------------- ------ ------ ----------------- ----------------- - ---------------- ----------- ---------------- ---------------- --------------------- ---- ---- ---------- ---------- -------------- ----------------- ---------- ----------- --------------- ------------------ ---------------- --------------------- - ------------- ----------------
    CON_ID
----------
           2         257       33554689  1017306195      16384       1723   28229632   33554432 CONTROLFILE                                                      UNPROT FINE   20190826 09:03:14 20201110 20:00:00 U rw-rw-rw-                  0                0                0                     0 COLD COLD          0          0              0                 0          0           0               0                  0                0                     0 N             0                0
         0
STRIPE  FINE 细粒度条带
SQL> @p _asm_stripesize
NAME
----------------------------------------
VALUE
----------------------------------------
_asm_stripesize
131072
SQL> @p _asm_stripewidth
NAME
----------------------------------------
VALUE
----------------------------------------
_asm_stripewidth
8

确定控制文件BLOCK 40 所在 AU,编号从0开始，计算得到BLOCK 40在AU5上。

代码语言：javascript
复制
In [14]: 16384.0*(40)/131072

Out[14]: 5.0

确定控制文件BLOCK 40 所在 AU 的第几个block。

代码语言：javascript
复制
In [17]: 16384.0*(40)%131072/16384

Out[17]: 0.0

将控制文件的AU5 DD出来,AU5就在asmdskemc15上(前面看到的热点盘)。

代码语言：javascript
复制
if=/dev/mapper/asmdskemc15 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=5 count=1

控制文件CP出来。

代码语言：javascript
复制
grid@test1:/home/grid$ asmcmd
ASMCMD> cp +DATADG/TEST/CONTROLFILE/current.257.1017306195 /home/grid/current.257.1017306195 
copying +DATADG/TEST/CONTROLFILE/current.257.1017306195 -> /home/grid/current.257.1017306195
ASMCMD> exit

对比刚刚自己计算，dd出来的块，可以确定与CP出来的一致，从而判断控制文件。BLOCK 40,42就在热点盘上，导致 control file sequential read 延迟增加，又因控制文件无缓存，该问题特别明显。

代码语言：javascript
复制
--control file cp 副本block 40

dd if=/home/grid/current.257.1017306195 bs=16384 skip=40 count=1 |hexdump -C|head -10
grid@hbods1:/home/grid$ dd if=/home/grid/current.257.1017306195 bs=16384 skip=40 count=1 |hexdump -C|head -10
1+0 records in
1+0 records out
16384 bytes (16 kB) copied, 0.000298896 s, 54.8 MB/s
00000000  15 c2 00 00 28 00 00 00  4f 09 66 00 ff ff 01 04  |....(...O.f.....|   <<<offset 4 block#   ,28 00 00 00  === 00000028 === 40
00000010  04 10 00 00 00 00 18 31  f3 68 80 20 31 02 40 00  |.......1.h. 1.@.|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 02  |................|
00000030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  18 00 c2 04 00 00 00 00  |................|
00000050  00 00 00 00 00 00 00 00  40 00 00 00 00 00 e0 be  |........@.......|
00000060  ff ff ff ff 3f ff ff ff  ff ff ff ff ff ff ff ff  |....?...........|
00000070  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff ff 00  |................|
00000080  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*
---从asm disk dd出的的 block 40
dd if=/tmp/file257_au6.dbf bs=16384 skip=0 count=1|hexdump -C| head -10
grid@hbods1:/home/grid$ dd if=/tmp/file257_au6.dbf bs=16384 skip=0 count=1|hexdump -C| head -10
1+0 records in
1+0 records out
16384 bytes (16 kB) copied, 0.000184142 s, 89.0 MB/s
00000000  15 c2 00 00 28 00 00 00  b1 07 66 00 ff ff 01 04  |....(.....f.....|  <<<offset 4 block#   ,28 00 00 00  === 00000028 === 40
00000010  0c 10 00 00 00 00 18 31  f3 68 80 20 31 02 40 00  |.......1.h. 1.@.|
00000020  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 02  |................|
00000030  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
00000040  00 00 00 00 00 00 00 00  10 00 42 04 00 00 00 00  |..........B.....|
00000050  00 00 00 00 00 00 00 00  40 00 00 00 00 00 e0 be  |........@.......|
00000060  ff ff ff ff 3f ff ff ff  ff ff ff ff ff ff ff ff  |....?...........|
00000070  ff ff ff ff ff ff ff ff  ff ff ff ff ff ff 7f 00  |................|
00000080  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
--ontrol file cp 副本 block 42
grid@hbods1:/home/grid$ dd if=/home/grid/current.257.1017306195 bs=16384 skip=42 count=1 |hexdump -C|head -10
1+0 records in
1+0 records out
16384 bytes (16 kB) copied, 0.00019117 s, 85.7 MB/s
00000000  15 c2 00 00 2a 00 00 00  4f 09 66 00 ff ff 01 04  |....*...O.f.....|
00000010  ee 5a 00 00 00 00 00 00  00 00 00 00 50 dc a2 3c  |.Z..........P..<|
00000020  48 42 4f 44 53 00 00 00  00 00 00 00 be 00 40 00  |HBODS.........@.|
00000030  00 40 40 00 00 00 00 00  00 00 00 00 01 00 00 00  |.@@.............|
00000040  00 00 00 00 50 dc a2 3c  00 00 00 00 00 00 00 00  |....P..<........|
00000050  00 00 00 00 00 00 00 00  00 00 00 13 b5 03 00 00  |................|
00000060  b5 03 00 00 02 00 00 00  85 3f 70 21 00 80 69 0f  |.........?p!..i.|
00000070  02 00 02 00 02 00 01 00  06 00 00 00 00 00 00 00  |................|
---从asm disk dd出的的 block 42
grid@hbods1:/home/grid$ dd if=/tmp/file257_au6.dbf bs=16384 skip=2 count=1|hexdump -C| head -10
1+0 records in
1+0 records out
16384 bytes (16 kB) copied, 0.000276328 s, 59.3 MB/s
00000000  15 c2 00 00 2a 00 00 00  b1 07 66 00 ff ff 01 04  |....*.....f.....|
00000010  fb bb 00 00 00 00 00 00  00 00 00 00 50 dc a2 3c  |............P..<|
00000020  48 42 4f 44 53 00 00 00  00 00 00 00 be 00 40 00  |HBODS.........@.|
00000030  00 40 40 00 00 00 00 00  00 00 00 00 01 00 00 00  |.@@.............|
00000040  00 00 00 00 50 dc a2 3c  00 00 00 00 00 00 00 00  |....P..<........|
00000050  00 00 00 00 00 00 00 00  00 00 00 13 b5 03 00 00  |................|
00000060  b5 03 00 00 02 00 00 00  85 3f 70 21 00 80 69 0f  |.........?p!..i.|
00000070  02 00 02 00 02 00 01 00  06 00 00 00 00 00 00 00  |................|
00000080  00 00 00 00 00 00 00 00  00 00 00 00 00 00 00 00  |................|
*

没错，40，42号块就在asmdskemc15这个盘上。

代码语言：javascript
复制
sdad                  65:208  0    2T  0 disk  
└─asmdskemc15        253:14   0    2T  0 mpath

sdad就是最忙的盘，busy 100%,延迟100ms以上，这个延迟已经很严重。 - end -

本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2021-03-15，如有侵权请联系 cloudcommunity@tencent.com 删除
网络安全
oracle
sql

本文分享自 数据和云 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

网络安全
oracle
sql
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
LV.
文章
0
获赞
0
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2024 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论