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

最新推荐文章于 2021-05-19 11:55:26 发布

数据和云

最新推荐文章于 2021-05-19 11:55:26 发布

阅读量269

点赞数

文章标签：定位数据库 launcher oracle firefox

本文链接：https://blog.csdn.net/enmotech/article/details/114859315

版权

墨墨导读：某客户检查表空间使用率的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。

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，可以看到控制文件相应的块重复产生物理读。

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。

SQL> select NAME from v$controlfile;NAME--------------------------------------------------+DATADG/HBODS/CONTROLFILE/current.257.1017306195SQL> select group_number,name from v$asm_diskgroup;GROUP_NUMBER NAME------------ ------------------------------           1 CRSDG           2 DATADG           3 DATADG2

确定AU大小。

grid@hbods1:/home/grid$ kfed read /dev/mapper/asmdskemc14
kfbh.endian:                          1 ; 0x000: 0x01kfbh.hard:                          130 ; 0x001: 0x82kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEADkfbh.datfmt:                          1 ; 0x003: 0x01kfbh.block.blk:                       0 ; 0x004: blk=0kfbh.block.obj:              2147483668 ; 0x008: disk=20kfbh.check:                  3074063759 ; 0x00c: 0xb73a7d8fkfbh.fcn.base:                 20675314 ; 0x010: 0x013b7af2kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000kfbh.spare1:                          0 ; 0x018: 0x00000000kfbh.spare2:                          0 ; 0x01c: 0x00000000kfdhdb.driver.provstr:         ORCLDISK ; 0x000: length=8kfdhdb.driver.reserved[0]:            0 ; 0x008: 0x00000000kfdhdb.driver.reserved[1]:            0 ; 0x00c: 0x00000000kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000kfdhdb.compat:                318767104 ; 0x020: 0x13000000kfdhdb.dsknum:                       20 ; 0x024: 0x0014kfdhdb.grptyp:                        1 ; 0x026: KFDGTP_EXTERNALkfdhdb.hdrsts:                        3 ; 0x027: KFDHDR_MEMBERkfdhdb.dskname:             DATADG_0020 ; 0x028: length=11kfdhdb.grpname:                  DATADG ; 0x048: length=6kfdhdb.fgname:              DATADG_0020 ; 0x068: length=11。。。。。。kfdhdb.secsize:                     512 ; 0x0b8: 0x0200kfdhdb.blksize:                    4096 ; 0x0ba: 0x1000kfdhdb.ausize:                  1048576 ; 0x0bc: 0x00100000  <<<au size

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

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_KFFXPINCARN_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=132 rows selected

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

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

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         0STRIPE  FINE 细粒度条带SQL> @p _asm_stripesizeNAME----------------------------------------VALUE----------------------------------------_asm_stripesize131072SQL> @p _asm_stripewidthNAME----------------------------------------VALUE----------------------------------------_asm_stripewidth8

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

In [14]: 16384.0*(40)/131072
Out[14]: 5.0

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

In [17]: 16384.0*(40)%131072/16384
Out[17]: 0.0

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

if=/dev/mapper/asmdskemc15 of=/tmp/file257.dbf conv=notrunc bs=1048576 skip=6 seek=5 count=1

控制文件CP出来。

grid@test1:/home/grid$ asmcmdASMCMD> cp +DATADG/TEST/CONTROLFILE/current.257.1017306195 /home/grid/current.257.1017306195 copying +DATADG/TEST/CONTROLFILE/current.257.1017306195 -> /home/grid/current.257.1017306195ASMCMD> exit

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

--control file cp 副本block 40
dd if=/home/grid/current.257.1017306195 bs=16384 skip=40 count=1 |hexdump -C|head -10grid@hbods1:/home/grid$ dd if=/home/grid/current.257.1017306195 bs=16384 skip=40 count=1 |hexdump -C|head -101+0 records in1+0 records out16384 bytes (16 kB) copied, 0.000298896 s, 54.8 MB/s00000000  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 === 4000000010  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 40dd if=/tmp/file257_au6.dbf bs=16384 skip=0 count=1|hexdump -C| head -10grid@hbods1:/home/grid$ dd if=/tmp/file257_au6.dbf bs=16384 skip=0 count=1|hexdump -C| head -101+0 records in1+0 records out16384 bytes (16 kB) copied, 0.000184142 s, 89.0 MB/s00000000  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 === 4000000010  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 42grid@hbods1:/home/grid$ dd if=/home/grid/current.257.1017306195 bs=16384 skip=42 count=1 |hexdump -C|head -101+0 records in1+0 records out16384 bytes (16 kB) copied, 0.00019117 s, 85.7 MB/s00000000  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 42grid@hbods1:/home/grid$ dd if=/tmp/file257_au6.dbf bs=16384 skip=2 count=1|hexdump -C| head -101+0 records in1+0 records out16384 bytes (16 kB) copied, 0.000276328 s, 59.3 MB/s00000000  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这个盘上。

sdad                  65:208  0    2T  0 disk  
└─asmdskemc15        253:14   0    2T  0 mpath

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

- end -

作者

范计杰，云和恩墨技术顾问，5年大型ORACLE数据库维护经验，擅长性能调优、故障处理等。

墨天轮原文链接：https://www.modb.pro/db/45742（复制到浏览器或者点击“阅读原文”立即查看）

推荐阅读：267页！2020年度数据库技术年刊

推荐下载：2020数据技术嘉年华PPT下载

2020数据技术嘉年华近50个PPT下载、视频回放已上传墨天轮平台，可在“数据和云”公众号回复关键词“2020DTC”获得！

视频号，新的分享时代，关注我们，看看有什么新发现？

数据和云

ID：OraNews

如有收获，请划至底部，点击“在看”，谢谢！

点击下图查看更多 ↓

云和恩墨大讲堂 | 一个分享交流的地方

长按，识别二维码，加入万人交流社群

请备注：云和恩墨大讲堂

点个“在看”

你的喜欢会被看到❤

数据和云

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫