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