Month: September 2017

SystemTap and Oracle RDBMS: I/O and Network Traffic

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.

Advertisement

SystemTap and Oracle RDBMS: Page Faults

Introduction

Now that I am able to aggregate SytemTap probes by Oracle database, let’s focus on page faults.

For this purpose a new SystemTap script (page_faults_per_db.stp) has been created and has been added into this github repository.

page_faults_per_db

This script tracks the page faults per database. It reports the total number of page faults and splits them into Major or Minor faults as well as Read or Write access.

Usage:

$> stap -g ./page_faults_per_db.stp <oracle uid> <refresh time ms>

Output example:

$> stap -g ./page_faults_per_db.stp 54321 10000

---------------------------------------------------------------------------------------
DBNAME      : READS_PFLT   WRITES_PFLT  TOTAL_PFLT   MAJOR_PFLT   MINOR_PFLT
---------------------------------------------------------------------------------------
BDT         : 30418        22526        52944        22           52922
NOT_A_DB    : 773          1088         1861         4            1858

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.
  • Should you be interested by this subject, then do read Hatem Mahmoud post.

Conclusion

We are able to track and group the page faults at the database level.