db_io_metrics

db_io_metrics.pl main page

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.
  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.

Examples:

Report the IO metrics per database instances and containers:

blog_db_io_metrics_containers_sort

 

Report the IO metrics per database instances and diskgroups:

blog_db_io_metrics_containers_per_dg

Report the IO metrics per database instances, containers and tablespaces:

blog_db_io_metrics_containers_tbs_sort

Blog posts related to it:

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

The last version of the db_io_metrics.pl is 1.1

You can download it from this repository or copy the source code from this page.

 

Advertisements

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