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

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

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_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=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/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 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/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 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（复制到浏览器或者点击“阅读原文”立即查看）

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

相关推荐