As you may know, there is some views available since 11g to monitor the dnfs activity:
- v$dnfs_servers: Shows a table of servers accessed using Direct NFS.
- v$dnfs_files: Shows a table of files now open with Direct NFS.
- v$dnfs_channels: Shows a table of open network paths (or channels) to servers for which Direct NFS is providing files.
- v$dnfs_stats: Shows a table of performance statistics for Direct NFS.
One interesting thing is that the v$dnfs_stats view provides two new columns as of 11.2.0.4:
- NFS_READBYTES: Number of bytes read from NFS server
- NFS_WRITEBYTES: Number of bytes written to NFS server
See the oracle documentation here.
Then, by sampling the view we can provide those metrics:
- Reads/s: Number of read per second.
- KbyRead/s: Kbytes read per second (as of 11.2.0.4).
- AvgBy/Read: Average bytes per read (as of 11.2.0.4).
- Writes/s: Number of Write per second.
- KbyWrite/s: Kbytes write per second (as of 11.2.0.4).
- AvgBy/Write: Average bytes per write (as of 11.2.0.4).
To do so, I just created a very simple db_io_dnfs_metrics.pl utility. It basically takes a snapshot each second (default interval) from the gv$dnfs_stats view and computes the delta with the previous snapshot. The utility is RAC aware.
Let’s see the help:
$>./db_io_dnfs_metrics.pl -help
Usage: ./db_io_dnfs_metrics.pl [-interval] [-count] [-inst] [-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
-DISPLAY= What to display: snap,avg (comma separated list) SNAP
-SORT_FIELD= reads|writes|iops NONE
Example: ./db_io_dnfs_metrics.pl
Example: ./db_io_dnfs_metrics.pl -inst=BDT_1
Example: ./db_io_dnfs_metrics.pl -sort_field=reads
This is a very simple utility, the options/features are:
- You can choose the number of snapshots to display and the time to wait between the snapshots.
- In case of RAC, you can choose on which database instance to collect the metrics thanks to the –INST= 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.
- In case of RAC, you can sort the instances based on the number of reads, number of writes, number of IOPS (reads+writes) thanks to the –SORT_FIELD= parameter.
Examples:
Collecting on a single Instance:
$>./db_io_dnfs_metrics.pl ............................ Collecting 1 sec.... ............................ ......... SNAP TAKEN AT ................... 14:26:18 Kby AvgBy/ Kby AvgBy/ 14:26:18 INST Reads/s Read/s Read Writes/s Write/s Write IOPS MB/s 14:26:18 ---------- ------- ------- ------- -------- ------- -------- -------- -------- 14:26:18 VSBDT 321 2568 8192 0 0 0 321 2.5 ............................ Collecting 1 sec.... ............................ ......... SNAP TAKEN AT ................... 14:26:19 Kby AvgBy/ Kby AvgBy/ 14:26:19 INST Reads/s Read/s Read Writes/s Write/s Write IOPS MB/s 14:26:19 ---------- ------- ------- ------- -------- ------- -------- -------- -------- 14:26:19 VSBDT 320 2560 8192 1 16 16384 321 2.5
Collecting on a RAC database, sorting the Instances by the number of read:
$>./db_io_dnfs_metrics.pl -sort_field=reads ............................ Collecting 1 sec.... ............................ ......... SNAP TAKEN AT ................... 17:21:21 Kby AvgBy/ Kby AvgBy/ 17:21:21 INST Reads/s Read/s Read Writes/s Write/s Write IOPS MB/s 17:21:21 ---------- ------- ------- ------- -------- ------- -------- -------- -------- 17:21:21 VBDTO_1 175 44536 260599 0 0 0 175 43.5 17:21:21 VBDTO_2 69 2272 33718 0 0 0 69 2.2 ............................ Collecting 1 sec.... ............................ ......... SNAP TAKEN AT ................... 17:21:22 Kby AvgBy/ Kby AvgBy/ 17:21:22 INST Reads/s Read/s Read Writes/s Write/s Write IOPS MB/s 17:21:22 ---------- ------- ------- ------- -------- ------- -------- -------- -------- 17:21:22 VBDTO_2 151 36976 250751 0 0 0 151 36.1 17:21:22 VBDTO_1 131 33408 261143 0 0 0 131 32.6 ............................ Collecting 1 sec.... ............................ ......... SNAP TAKEN AT ................... 17:21:23 Kby AvgBy/ Kby AvgBy/ 17:21:23 INST Reads/s Read/s Read Writes/s Write/s Write IOPS MB/s 17:21:23 ---------- ------- ------- ------- -------- ------- -------- -------- -------- 17:21:23 VBDTO_2 133 33592 258633 0 0 0 133 32.8 17:21:23 VBDTO_1 121 31360 265394 0 0 0 121 30.6
Remarks:
- The utility works only as of 11.2.0.4.
- You can download it from this repository or copy the source code from this page.
- This utility joins the db_io* family.
- For version prior to 11.2.0.4, you may find Glenn Fawcett’s script useful.
bdt's blog 