Now that I am able to aggregate SytemTap probes by Oracle database, let’s focus on I/O and Network Traffic.
For this purpose a new SystemTap script (traffic_per_db.stp) has been created and has been added into this github repository.
traffic_per_db
This script tracks the I/O and Network traffic per database and also groups the non database(s) traffic.
Usage:
$> stap -g ./traffic_per_db.stp <oracle uid> <refresh time ms> <io|network|both>
Output example (I/O only):
$> stap -g ./traffic_per_db.stp 54321 5000 io
-------------------------------------------------------------------------------------------------------------
| I/O |
-------------------------------------------------------------------------------------------------------------
READS | WRITES |
| |
VFS BLOCK | VFS BLOCK |
| NB KB | NB KB | NB KB | NB KB |
| -- -- | -- -- | -- -- | -- -- |
BDTS | 6203 49280 | 0 0 | 11 64 | 12 128 |
NOT_A_DB | 45 3 | 0 0 | 15 2 | 0 0 |
-------------------------------------------------------------------------------------------------------------
For this example the database files are located on a file system. In this output, we can see than the reads I/O are served by the file system cache: Reads VFS I/O and no Reads BLOCK I/O.
Output example (Network only):
Example 1: The database files are located on Kernel NFS (kNFS)
$> stap -g ./traffic_per_db.stp 54321 5000 network
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Network |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
RECV | SENT |
| |
TCP UDP NFS | TCP UDP NFS |
| NB KB | NB KB | NB KB | NB KB | NB KB | NB KB |
| -- -- | -- -- | -- -- | -- -- | -- -- | -- -- |
NOT_A_DB | 4 32 | 0 0 | 61 706 | 1943 252 | 0 0 | 5 80 |
BVDB | 0 0 | 0 0 | 1623 16825 | 113 13 | 0 0 | 170 1437 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
As expected we can observed NFS traffic at the database level.
Example 2: The database files are located on Direct NFS (dNFS)
$> stap -g ./traffic_per_db.stp 54321 5000 network
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Network |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
RECV | SENT |
| |
TCP UDP NFS | TCP UDP NFS |
| NB KB | NB KB | NB KB | NB KB | NB KB | NB KB |
| -- -- | -- -- | -- -- | -- -- | -- -- | -- -- |
BVDB | 3810 18934 | 0 0 | 0 0 | 2059 1787 | 0 0 | 0 0 |
NOT_A_DB | 3 24 | 0 0 | 0 0 | 4 2 | 0 0 | 0 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
As you can see the NFS traffic has been replaced by a TCP traffic at the database level.
Remarks
- In this post the word database stands for “all the foreground and background processes linked to an oracle database”.
- In a consolidated environment, having a view per database can be very useful.
- This script helps to display the traffic per database and also reports the one that is not related to databases (“NOT_A_DB”).
- The probes documentation can be found here.
Conclusion
We are able to track the I/O and the Network traffic at the database level.