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

转载

mob604756fa96d7 2021-06-04 12:48:44

文章标签 Jav 文章分类 Java 后端开发

范计杰数据和云

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

从AWR中看到control file sequential read为TOP EVENT,占了89%的DB TIME.对比之前的Waits，并没有明显增加，但延迟从us级别增加到ms级别，增长明显。实战经验：如何定位控制文件热点块，即读取延迟高的块所在的ASM磁盘_Jav
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读取的块在热点盘上。实战经验：如何定位控制文件热点块，即读取延迟高的块所在的ASM磁盘_Jav_03

知识点

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=67389713130755WAIT #140285876517504: nam='control file sequential read' ela= 4 file#=0 block#=17 blocks=1 obj#=-1 tim=67389713130769WAIT #140285876517504: nam='control file sequential read' ela= 5 file#=0 block#=90 blocks=1 obj#=-1 tim=67389713130785WAIT #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=67389714364091WAIT #140285876517504: nam='control file sequential read' ela= 6 file#=0 block#=15 blocks=1 obj#=-1 tim=67389714364128WAIT #140285876517504: nam='control file sequential read' ela= 5 file#=0 block#=17 blocks=1 obj#=-1 tim=67389714364152WAIT #140285876517504: nam='control file sequential read' ela= 7 file#=0 block#=90 blocks=1 obj#=-1 tim=67389714364177WAIT #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
实战经验：如何定位控制文件热点块，即读取延迟高的块所在的ASM磁盘_Jav_05

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

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

查看ontrol file asm file number。实战经验：如何定位控制文件热点块，即读取延迟高的块所在的ASM磁盘_Jav_07

确定AU大小。 7
查看控制文件每个AU对应的磁盘，这里借用了之前dd抽取asm文件的一个角本。实战经验：如何定位控制文件热点块，即读取延迟高的块所在的ASM磁盘_Jav_08

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

查看控制文件每个AU对应的磁盘，这里借用了之前dd抽取asm文件的一个角本。实战经验：如何定位控制文件热点块，即读取延迟高的块所在的ASM磁盘_Jav_10

确定控制文件条带粒度，精细条带的大小，条带的宽度，用来计算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（复制到浏览器或者点击“阅读原文”立即查看）