Find out the most physical IO consumers through ASM in real time

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.

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.

Remarks:

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.