Well, suppose you are using ASM and it is servicing a lot of databases per machine. Suddenly the number of IOPS increased in such a way that your sysadmin/storage guy warn you up (I know this is not the real life, we are supposing 😉 ).
So I would like to find out which database(s) are responsible for this load. Of course I could connect on each database and check the oracle statistics but there is a simpler/faster way: Extract this information from ASM.
Into a previous post ASM I/O Statistics Utility V2, I introduced a new feature of my asmiostat utility: ability to sort on reads or writes.
I just added a new sort option: iops (which is simply Reads/s+Writes/s).
Let’s see the help:
./real_time.pl -type=asmiostat -h Usage: ./real_time.pl -type=asmiostat [-interval] [-count] [-inst] [-dbinst] [-dg] [-fg] [-ip] [-show] [-sort_field] [-help] Default Interval : 1 second. Default Count : Unlimited Parameter Comment Default --------- ------- ------- -INST= ALL - Show all Instance(s) ALL CURRENT - Show Current Instance INSTANCE_NAME,... - choose Instance(s) to display -DBINST= Database Instance to collect (Wildcard allowed) ALL -DG= Diskgroup to collect (comma separated list) ALL -FG= Failgroup to collect (comma separated list) ALL -IP= IP (Exadata Cells) to collect (Wildcard allowed) ALL -SHOW= What to show: inst,dbinst,fg|ip,dg,dsk (comma separated list) DG -SORT_FIELD= reads|writes|iops NONE Example: ./real_time.pl -type=asmiostat Example: ./real_time.pl -type=asmiostat -inst=+ASM1 Example: ./real_time.pl -type=asmiostat -dg=DATA -show=dg Example: ./real_time.pl -type=asmiostat -dg=data -show=inst,dg,fg Example: ./real_time.pl -type=asmiostat -show=dg,dsk Example: ./real_time.pl -type=asmiostat -show=inst,dg,fg,dsk Example: ./real_time.pl -type=asmiostat -interval=5 -count=3 -sort_field=iops
As you can see, we can now sort on iops.
Let’s now launch the script to retrieve the databases ordered by iops in real time:
- Using -show=dbinst option as I want to see the databases.
- Using -sort_field=iops option as I want to order by iops.
./real_time.pl -type=asmiostat -show=dbinst -sort_field=iops ............................ Collecting 1 sec.... ............................ 17:11:51 Kby Avg AvgBy/ Kby Avg AvgBy/ 17:11:51 INST DBINST DG FG DSK Reads/s Read/s ms/Read Read Writes/s Write/s ms/Write Write 17:11:51 ------ ----------- ----------- ----------- ---------- ------- ------ ------- ------ ------ ------- -------- ------ 17:11:51 IATEBDTO_1 575 63696 2.2 113434 56 896 1.9 16384 17:11:51 SMTBDTO_2 577 28816 1.1 51140 0 0 0.0 0 17:11:51 BDTO_1 59 920 0.3 15967 30 464 1.7 15838 17:11:51 BDTO_2 3 48 0.6 16384 0 0 0.0 0 17:11:51 BKP10GR2_1 2 32 0.0 16384 0 0 0.0 0 17:11:51 JCAASM_1 2 32 0.9 16384 0 0 0.0 0 17:11:51 MILASM_1 2 32 0.4 16384 0 0 0.0 0
As you can see the IATEBDTO_1 instance is the one that recorded the most physical IO activity (Reads/s + Writes/s) during the last second.
So we are able to find out quickly which databases are the most physical IO consumers in real time thanks to the ASM metrics.
- This is real time information: the script takes a snapshot each second (default interval) from the gv$asm_disk_iostat (or gv$asm_disk_stat depending of the version) cumulative view and computes the delta with the previous snapshot.
- You can display the database instances (show=dbinst) as of 11gr1 (as it is based on the gv$asm_disk_iostat view).
- You can also find out which host is the most responsible for the physical IO thanks to the show=inst option (as it will display the ASM instances) (if not a 12c Flex ASM).
- You can also find out which diskgroup is the most responsible for the physical IO thanks to the show=dg option.
- You can also find out…. (I let you finish the sentence following your needs: failgroups, disks, databases per diskgroup… as the asmiostat utility output is customizable (see this post).
- You can download the asmiostat utility (which is part of the real_time.pl script) from this repository.
UPDATE: The asmiostat utility is not part of the real_time.pl script anymore. A new utility called asm_metrics.pl has been created. See “ASM metrics are a gold mine. Welcome to asm_metrics.pl, a new utility to extract and to manipulate them in real time” for more information.