db_io*metrics family

Into the last three posts I introduced three utilities to report database physical IO metrics in real time.

I just want to introduce again those three utilities into a single post as:

You may have missed one of them.
They are all members of the same family.

The three utilities are:

db_io_metrics for reads and writes metrics related to data files and temp files.
db_io_type_metrics for reads, writes, small, large and synchronous metrics related to data files, temp files,control files, log files, archive logs, and so on.
db_io_function_metrics for reads, writes, small and large metrics related to database functions (LGWR, DBWR, Smart Scan and so on).

If a new member join this family, I will let you know.

Remarks:

One member of the family looks like SLOB‘s logo (see below its “official” logo) designed by flashdba‘s childs, don’t you think? 😉

If you are interested in IO metrics related to ASM, you can have a look to the asm_metrics utility.

Report database physical IO activity per database function (LGWR, DBWR, Smart Scan…) and IO type in real time

Welcome to db_io_function_metrics, an utility used to display database physical IO per database functions (DBWR, LGWR, Direct Reads, Smart Scan and so on) real-time metrics. It basically takes a snapshot each second (default interval) from the gv$iostat_function cumulative view and computes the delta with the previous snapshot. The utility is RAC aware (No need to be multitenant aware as con_id=0 into gv$iostat_function for 12.1 in any cases even if pdbs are created).

This utility:

provides useful metrics.
is RAC aware.
is fully customizable: you can aggregate the results depending on your needs.
does not install anything into the database.

It displays the following metrics per IO Type (small, large, reads and writes) and database functions:

MB/s: Megabytes per second.
RQ/s: Requests per second.
Avg MB/RQ: Average Megabytes per request.
Avg ms/RQ: Average ms per request.

At the following levels:

Database Instance.
Function

Let’s see the help:

./db_io_function_metrics.pl -help

Usage: ./db_io_function_metrics.pl [-interval] [-count] [-inst] [-function] [-io_type] [-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
  -FUNCTION=        IO Function to collect (wildcard allowed)                                   ALL
  -IO_TYPE=         IO Type to collect: reads,writes,small,large                                READS
  -SHOW=            What to show: inst,function (comma separated list)                          INST
  -DISPLAY=         What to display: snap,avg (comma separated list)                            SNAP
  -SORT_FIELD=      small_reads,small_writes,large_reads,large_writes                           NONE

Example: ./db_io_function_metrics.pl
Example: ./db_io_function_metrics.pl  -inst=CBDT1
Example: ./db_io_function_metrics.pl  -show=inst,function
Example: ./db_io_function_metrics.pl  -show=inst,function -function=%Direct%
Example: ./db_io_function_metrics.pl  -show=inst,function -function=%Direct% -io_type=small -sort_field=small_reads

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 Function to collect the metric thanks to the -FUNCTION=parameter (wilcard allowed).
You can choose on which IO Type to collect the metrics thanks to the -IO_TYPE= parameter.
You can aggregate the results on the database instances and database functions 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 small_reads, number of small_writes, number of large_reads or number of large_writes thanks to the –SORT_FIELD= parameter.

Examples:

Report the IO metrics for “small” IO per database instances and functions during a SLOB run:

./db_io_function_metrics.pl -show=inst,function -io_type=small
............................
Collecting 1 sec....
............................

......... SNAP TAKEN AT ...................

09:49:55                                       SMALL R   SMALL R   SMALL W   SMALL W   Avg MB/   Avg MB/   R+W       Avg ms/
09:49:55   INST         FUNCTION               MB/s      RQ/s      MB/s      RQ/s      SMALL R   SMALL W   IO/s      R+W IO
09:49:55   ----------   --------------------   -------   -------   -------   -------   -------   -------   -------   -------
09:49:55   BDT_1                               147       18817     0         2         0.008     0.000     18847     0.04
09:49:55   BDT_1        ARCH                   0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Archive Manager        0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Buffer Cache Reads     147       18811     0         0         0.008     0.000     18843     0.04
09:49:55   BDT_1        DBWR                   0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Data Pump              0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Direct Reads           0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Direct Writes          0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        LGWR                   0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Others                 0         6         0         2         0.000     0.000     4         0.25
09:49:55   BDT_1        RMAN                   0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Recovery               0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Smart Scan             0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        Streams AQ             0         0         0         0         0.000     0.000     0         0.00
09:49:55   BDT_1        XDB                    0         0         0         0         0.000     0.000     0         0.00

Report the IO metrics for “reads” IO per database instances and functions for the RMAN function during RMAN backup:

./db_io_function_metrics.pl -show=inst,function -io_type=reads -function=RMAN
............................
Collecting 1 sec....
............................

......... SNAP TAKEN AT ...................

10:04:58                                       SMALL R   SMALL R   LARGE R   LARGE R   Avg MB/   Avg MB/   R+W       Avg ms/
10:04:58   INST         FUNCTION               MB/s      RQ/s      MB/s      RQ/s      SMALL R   LARGE R   IO/s      R+W IO
10:04:58   ----------   --------------------   -------   -------   -------   -------   -------   -------   -------   ---------
10:04:58   BDT_1                               3         159       248       62        0.019     4.0       176       1.10
10:04:58   BDT_1        RMAN                   3         159       248       62        0.019     4.0       176       1.10

You can also report the metrics for Smart Scan operations, Data Pump operations and so on…

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

Conclusion:

Four utilities are now available to report IO metrics in real time depending on our needs:

asm_metrics for reads and writes metrics related to ASM.
db_io_metrics for reads and writes metrics related to data files and temp files.
db_io_type_metrics for reads, writes, small, large and synchronous metrics related to data files, temp files,control files, log files, archive logs, and so on.
db_io_function_metrics for reads, writes, small and large metrics related to database functions (LGWR, DBWR, Smart Scan and so on).

Report database physical IO activity per IO type and oracle files type in real time

Introduction

After I published the blog post related to the db_io_metrics utiliy (based on gv$filestat and gv$tempstat), Luca Canali suggested me to create a version based on the gv$iostat_file cumulative view (available since 11.1).

The advantages of this view are:

it displays information about disk I/O statistics on a lot of file types (including data files, temp files,control files, log files, archive logs, and so on).
it takes care of the IO type (small,large,reads, writes and synchronous).

Then based on this, I created a new utility: db_io_type_metrics

db_io_type_metrics description

The db_io_type_metrics.pl utility is used to display database physical IO type (small, large, synchronous, reads and writes) real-time metrics. It basically takes a snapshot each second (default interval) from the gv$iostat_file cumulative view 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 per IO Type (small, large, synchronous, reads and writes):

MB/s: Megabytes per second.
RQ/s: Requests per second.
Avg MB/RQ: Average Megabytes per request.
Avg ms/RQ: Average ms per request.

At the following levels:

Database Instance.
Database container.
File Type (log file, control file, data file, temp file…) or tablespace.

Let’s see the help:

./db_io_type_metrics.pl -help

Usage: ./db_io_type_metrics.pl [-interval] [-count] [-inst] [-cont] [-file_type_tbs] [-io_type] [-file_type] [-tbs] [-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
  -FILE_TYPE_TBS=   Collect on File Type or on Tablespace: file_type,tbs                        FILE_TYPE
  -IO_TYPE=         IO Type to collect: reads,writes,small,large,synch                          READS
  -FILE_TYPE=       File Type to collect (in case FILE_TYPE_TBS=file_type) (wildcard allowed)   NONE
  -TBS=             Tablespace to collect (in case FILE_TYPE_TBS=tbs) (wildcard allowed)        NONE
  -SHOW=            What to show: inst,cont,file_type_tbs (comma separated list)                INST
  -DISPLAY=         What to display: snap,avg (comma separated list)                            SNAP
  -SORT_FIELD=      small_reads,small_writes,large_reads,large_writes                           NONE

Example: ./db_io_type_metrics.pl
Example: ./db_io_type_metrics.pl  -inst=CBDT1
Example: ./db_io_type_metrics.pl  -show=inst,file_type_tbs
Example: ./db_io_type_metrics.pl  -show=inst,file_type_tbs -file_type=%Data%
Example: ./db_io_type_metrics.pl  -show=inst -io_type=large
Example: ./db_io_type_metrics.pl  -show=inst -io_type=small -sort_field=small_reads
Example: ./db_io_type_metrics.pl  -show=inst,file_type_tbs -file_type_tbs=tbs -tbs=%USE%
Example: ./db_io_type_metrics.pl  -show=inst,cont
Example: ./db_io_type_metrics.pl  -show=inst,cont -cont=%P%
Example: ./db_io_type_metrics.pl  -show=inst,cont,file_type_tbs -io_type=small -sort_field=small_reads

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.
You can choose to collect on File Type or tablespace thanks to the -FILE_TYPE_TBS=parameter.
You can choose on which IO Type to collect the metrics thanks to the -IO_TYPE= parameter.
You can choose on which File Type to collect the metric thanks to the -FILE_TYPE=parameter (wilcard allowed).
You can choose on which Tablespace to collect the metric thanks to the -TBS=parameter (wilcard allowed).
You can aggregate the results on the database instances, containers, file type or tablespace 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 small_reads, number of small_writes, number of large_reads or number of large_writes thanks to the –SORT_FIELD= parameter.

Let’s see some use cases:

Report the IO type metrics for “reads” IO per database instances, tablespaces for the SLOB tablespace:

 ./db_io_type_metrics.pl  -show=inst,file_type_tbs -file_type_tbs=tbs -tbs=%SLOB% -io_type=reads
............................
Collecting 1 sec....
............................

......... SNAP TAKEN AT ...................

10:52:54                                               SMALL R   SMALL R   LARGE R   LARGE R   Avg MB/   Avg MB/   Avg ms/   Avg ms/
10:52:54   INST         FILE_TYPE_TBS                  MB/s      RQ/s      MB/s      RQ/s      SMALL R   LARGE R   SMALL R   LARGE R
10:52:54   ----------   ----------------------------   -------   -------   -------   -------   -------   -------   -------   -------
10:52:54   BDT_1                                       144       18390     0         0         0.008     0.0       0.08      0.00
10:52:54   BDT_1        SLOB                           144       18390     0         0         0.008     0.0       0.08      0.00

Report the IO type metrics for “small” IO” and all file type:

./db_io_type_metrics.pl  -show=file_type_tbs -file_type_tbs=file_type -io_type=small
............................
Collecting 1 sec....
............................

......... SNAP TAKEN AT ...................

10:57:01                                               SMALL R   SMALL R   SMALL W   SMALL W   Avg MB/   Avg MB/   Avg ms/   Avg ms/
10:57:01   INST         FILE_TYPE_TBS                  MB/s      RQ/s      MB/s      RQ/s      SMALL R   SMALL W   SMALL R   SMALL W
10:57:01   ----------   ----------------------------   -------   -------   -------   -------   -------   -------   -------   -------
10:57:01                Archive Log                    0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Archive Log Backup             0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Control File                   1         66        0         0         0.015     0.000     0.00      0.00
10:57:01                Data File                      125       16230     0         0         0.008     0.000     0.15      0.00
10:57:01                Data File Backup               0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Data File Copy                 0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Data File Incremental Backup   0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Data Pump Dump File            0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Flashback Log                  0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Log File                       0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Other                          0         0         0         0         0.000     0.000     0.00      0.00
10:57:01                Temp File                      0         0         0         0         0.000     0.000     0.00      0.00

Report the IO type metrics for “small” IO per database instances and containers:

./db_io_type_metrics.pl -show=inst,cont -io_type=small
............................
Collecting 1 sec....
............................

......... SNAP TAKEN AT ...................

13:07:46                                                                 SMALL R   SMALL R   SMALL W   SMALL W   Avg MB/   Avg MB/   Avg ms/   Avg ms/
13:07:46   INST         CONT              FILE_TYPE_TBS                  MB/s      RQ/s      MB/s      RQ/s      SMALL R   SMALL W   SMALL R   SMALL W
13:07:46   ----------   ---------------   ----------------------------   -------   -------   -------   -------   -------   -------   -------   -------
13:07:46   CBDT1                                                         2         97        0         1         0.021     0.000     0.03      1.00
13:07:46   CBDT1        CDB$ROOT                                         2         97        0         1         0.021     0.000     0.03      1.00
13:07:46   CBDT1        PDB$SEED                                         0         0         0         0         0.000     0.000     0.00      0.00
13:07:46   CBDT1        P_1                                              0         0         0         0         0.000     0.000     0.00      0.00
13:07:46   CBDT1        P_2                                              0         0         0         0         0.000     0.000     0.00      0.00
13:07:46   CBDT1        P_3                                              0         0         0         0         0.000     0.000     0.00      0.00
13:07:46   CBDT2                                                         1         96        0         1         0.010     0.000     0.09      2.00
13:07:46   CBDT2        CDB$ROOT                                         1         96        0         1         0.010     0.000     0.09      2.00
13:07:46   CBDT2        PDB$SEED                                         0         0         0         0         0.000     0.000     0.00      0.00
13:07:46   CBDT2        P_1                                              0         0         0         0         0.000     0.000     0.00      0.00
13:07:46   CBDT2        P_2                                              0         0         0         0         0.000     0.000     0.00      0.00
13:07:46   CBDT2        P_3                                              0         0         0         0         0.000     0.000     0.00      0.00

Report the IO type metrics per… I let you finish the sentence as the utility is customizable enough 😉

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

Conclusion:

Three utilities are now available to report IO metrics in real time depending on our needs:

asm_metrics for reads and writes metrics related to ASM.
db_io_metrics for reads and writes metrics related to data files and temp files.
db_io_type_metrics for reads, writes, small, large and synchronous metrics related to data files, temp files,control files, log files, archive logs, and so on.

Enjoy and don’t hesitate to come back to me in case of questions, issues or suggestions.