The db_io_metrics.pl utility is used to display database physical IO real-time metrics. It basically takes a snapshot each second (default interval) from the gv$filestat and gv$tempstat cumulative views and computes the delta with the previous snapshot. The utility is RAC and Multitenant aware.
This utility:
- provides useful metrics.
- is RAC aware.
- detects if it is connected to a multitenant database and then is able to display the containers metrics.
- is fully customizable: you can aggregate the results depending on your needs.
- does not install anything into the database.
It displays the following metrics:
- Reads/s: Number of read per second.
- KbyRead/s: Kbytes read per second.
- Avg ms/Read: ms per read in average.
- AvgBy/Read: Average Bytes per read.
- Same metrics are provided for Write Operations.
At the following levels:
- Database Instance.
- Database container.
- Filesystem or ASM Diskgroup.
- Tablespace.
- Datafile.
Let’s see the help:
./db_io_metrics.pl -help
Usage: ./db_io_metrics.pl [-interval] [-count] [-inst] [-cont] [-fs_dg] [-tbs] [-file] [-fs_delimiter] [-show] [-display] [-sort_field] [-help]
Default Interval : 1 second.
Default Count : Unlimited
Parameter Comment Default
--------- ------- -------
-INST= ALL - Show all Instance(s) ALL
CURRENT - Show Current Instance
-CONT= Container to collect (wildcard allowed) ALL
-FS_DG= Filesystem or Diskgroup to collect (wildcard allowed) ALL
-TBS= Tablespace to collect (wildcard allowed) ALL
-FILE= Datafile to collect (wildcard allowed) ALL
-FS_DELIMITER= Folder which follows the FS mount point ORADATA
-SHOW= What to show: inst,cont,fs_dg,tbs,file (comma separated list) INST
-DISPLAY= What to display: snap,avg (comma separated list) SNAP
-SORT_FIELD= reads|writes|iops NONE
Example: ./db_io_metrics.pl
Example: ./db_io_metrics.pl -inst=BDT_1
Example: ./db_io_metrics.pl -show=inst,tbs
Example: ./db_io_metrics.pl -show=inst,tbs -tbs=%UNDO%
Example: ./db_io_metrics.pl -show=fs_dg
Example: ./db_io_metrics.pl -show=inst,fs_dg -display=avg
Example: ./db_io_metrics.pl -show=inst,fs_dg -sort_field=reads
Example: ./db_io_metrics.pl -show=inst,tbs,cont
Example: ./db_io_metrics.pl -show=inst,tbs,cont -cont=%P_%
Example: ./db_io_metrics.pl -show=inst,cont -sort_field=iops
The main options/features are:
- You can choose the number of snapshots to display and the time to wait between snapshots.
- You can choose on which database instance to collect the metrics thanks to the -INST= parameter.
- You can choose on which database container to collect the metrics thanks to the -CONT= parameter (wilcard allowed).
- You can choose on which Diskgroup or Filesystem to collect the metrics thanks to the -FS_DG= parameter (wildcard allowed).
- You can choose on which tablespace to collect the metrics thanks to the -TBS= parameter (wildcard allowed).
- You can choose on which datafile to collect the metrics thanks to the -FILE= parameter (wildcard allowed).
- You can choose which folder is your Filesystem delimiter thanks to the -FS_DELIMITER= parameter.
- You can aggregate the results on the database instances, containers, diskgroups or filesystems, tablespaces level thanks to the -SHOW= parameter.
- You can display the metrics per snapshot, the average metrics value since the collection began (that is to say since the script has been launched) or both thanks to the -DISPLAY= parameter.
- You can sort based on the number of reads, number of writes or number of IOPS (reads+writes) thanks to the -SORT_FIELD= parameter.
Let’s see some use cases:
Report the physical IO metrics for the database instances:
./db_io_metrics.pl -show=inst
............................
Collecting 1 sec....
............................
......... SNAP TAKEN AT ...................
12:55:31 Kby Avg AvgBy/ Kby Avg AvgBy/
12:55:31 INST CONT FS_DG TBS FILE Reads/s Read/s ms/Read Read Writes/s Write/s ms/Write Write
12:55:31 ---------- ---------- -------- ------------ ------------ ------- ------ ------- ------ -------- ------- -------- ------
12:55:31 CBDT1 376.0 3008 5.8 8192 0.0 0 0.0 0
12:55:31 CBDT2 346.0 2768 15.6 8192 0.0 0 0.0 0
Report the physical IO metrics per database instances and per containers and sort by iops:
./db_io_metrics.pl -show=inst,cont -sort_field=iops
............................
Collecting 1 sec....
............................
......... SNAP TAKEN AT ...................
12:57:59 Kby Avg AvgBy/ Kby Avg AvgBy/
12:57:59 INST CONT FS_DG TBS FILE Reads/s Read/s ms/Read Read Writes/s Write/s ms/Write Write
12:57:59 ---------- ---------- -------- ------------ ------------ ------- ------ ------- ------ -------- ------- -------- ------
12:57:59 CBDT2 293.0 2344 18.8 8192 0.0 0 0.0 0
12:57:59 CBDT2 CDB$ROOT 150.0 1200 18.4 8192 0.0 0 0.0 0
12:57:59 CBDT2 P_1 143.0 1144 19.2 8192 0.0 0 0.0 0
12:57:59 CBDT2 PDB$SEED 0.0 0 0.0 0 0.0 0 0.0 0
12:57:59 CBDT2 P_2 0.0 0 0.0 0 0.0 0 0.0 0
12:57:59 CBDT2 P_3 0.0 0 0.0 0 0.0 0 0.0 0
12:57:59 CBDT1 274.0 2192 9.1 8192 0.0 0 0.0 0
12:57:59 CBDT1 CDB$ROOT 274.0 2192 9.1 8192 0.0 0 0.0 0
12:57:59 CBDT1 PDB$SEED 0.0 0 0.0 0 0.0 0 0.0 0
12:57:59 CBDT1 P_1 0.0 0 0.0 0 0.0 0 0.0 0
12:57:59 CBDT1 P_2 0.0 0 0.0 0 0.0 0 0.0 0
12:57:59 CBDT1 P_3 0.0 0 0.0 0 0.0 0 0.0 0
Report the physical IO metrics per database instances and per filesystem or ASM diskgroup:
./db_io_metrics.pl -show=inst,fs_dg
............................
Collecting 1 sec....
............................
......... SNAP TAKEN AT ...................
13:00:39 Kby Avg AvgBy/ Kby Avg AvgBy/
13:00:39 INST CONT FS_DG TBS FILE Reads/s Read/s ms/Read Read Writes/s Write/s ms/Write Write
13:00:39 ---------- ---------- -------- ------------ ------------ ------- ------ ------- ------ -------- ------- -------- ------
13:00:39 CBDT1 349.0 2792 8.3 8192 0.0 0 0.0 0
13:00:39 CBDT1 DATA 349.0 2792 8.3 8192 0.0 0 0.0 0
13:00:39 CBDT2 272.0 2176 25.4 8192 0.0 0 0.0 0
13:00:39 CBDT2 DATA 272.0 2176 25.4 8192 0.0 0 0.0 0
Report the physical IO metrics per… I let you finish the sentence as the utility is customizable enough ;-)
Remarks:
- The metrics are “only” linked to the datafiles and tempfiles (See the oracle documentation for more details).
- In case your database is on Filesystems, you have to change the “FS_DELIMITER” argument to aggregate the metrics at the Filesystem level. For example, if the FS are :
Filesystem size used avail capacity Mounted on
devora11-data/u500 960G 927G 33G 97% /ec/dev/server/oracle/devora11/u500
devora11-data/u501 960G 767G 193G 80% /ec/dev/server/oracle/devora11/u501
devora11-data/u502 500G 445G 55G 89% /ec/dev/server/oracle/devora11/u502
And the datafiles are located “behind” the oradata folder:
/ec/dev/server/oracle/devora11/u500/oradata/BDT
/ec/dev/server/oracle/devora11/u501/oradata/BDT
/ec/dev/server/oracle/devora11/u502/oradata/BDT
Then you can launch the utility that way:
./db_io_metrics.pl -show=inst,fs_dg -fs_delimiter=oradata
............................
Collecting 1 sec....
............................
......... SNAP TAKEN AT ...................
11:00:35 Kby Avg AvgBy/ Kby Avg AvgBy/
11:00:35 INST FS_DG TBS FILE Reads/s Read/s ms/Read Read Writes/s Write/s ms/Write Write
11:00:35 ---------- ---------------- -------------- -------------- ------- ------ ------- ------ -------- ------- -------- ------
11:00:35 BDT 129.0 1032 8.3 8192 0.0 0 0.0 0
11:00:35 BDT /ec/dev/server/oracle/devora11/u500/ 67.0 536 10.0 8192 0.0 0 0.0 0
11:00:35 BDT /ec/dev/server/oracle/devora11/u501/ 22.0 176 5.5 8192 0.0 0 0.0 0
11:00:35 BDT /ec/dev/server/oracle/devora11/u502/ 40.0 320 7.0 8192 0.0 0 0.0 0
- If you don’t want to see the FS (do not use -show=fs_dg), then there is no need to specify the -fs_delimiter argument.
- Reading the good article A Closer Look at CALIBRATE_IO from Luca Canali gave me the idea to create this utility.
- If you are interested in those real-time metrics at the ASM level, you can have a look to the asm_metrics utility.
You can download it from this repository or copy the source code from this page.