Welcome to db_io_metrics, a new utility to display database physical IO metrics in real time

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:

  1. You can choose the number of snapshots to display and the time to wait between snapshots.
  2. You can choose on which database instance to collect the metrics thanks to the -INST= parameter.
  3. You can choose on which database container to collect the metrics thanks to the -CONT= parameter (wilcard allowed).
  4. You can choose on which Diskgroup or Filesystem to collect the metrics thanks to the -FS_DG= parameter (wildcard allowed).
  5. You can choose on which tablespace to collect the metrics thanks to the -TBS= parameter (wildcard allowed).
  6. You can choose on which datafile to collect the metrics thanks to the -FILE= parameter (wildcard allowed).
  7. You can choose which folder is your Filesystem delimiter thanks to the -FS_DELIMITER= parameter.
  8. You can aggregate the results on the database instances, containers, diskgroups or filesystems, tablespaces level thanks to the -SHOW= parameter.
  9. 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.
  10. 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.

About these ads
This entry was posted in Perl Scripts. Bookmark the permalink.

2 Responses to Welcome to db_io_metrics, a new utility to display database physical IO metrics in real time

  1. kaisyasmine says:

    Very good work ,thanks !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s