Introduction

Now that I am able to aggregate SytemTap probes by Oracle database, it’s time to create several scripts in a toolkit. The toolkit is available in this github repository.

Let’s describe 3 new members of the toolkit:

  • schedtimes_per_db.stp: To track time databases spend in various states
  • vfsio_per_db.stp: To track I/O by database through the Virtual File System (vfs) layer
  • blkio_per_db.stp: To track I/O by database through the block IO layer

schedtimes_per_db

This script tracks the time databases spend in various states. It also reports the time spend by non oracle database.

Usage:

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

Output example:

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

------------------------------------------------------------------
DBNAME    :    run(us)  sleep(us) iowait(us) queued(us)  total(us)
------------------------------------------------------------------
NOT_A_DB  :     447327  200561911       1328     517522  201528088
BDT       :      42277  316189082          0      69355  316300714
VBDT      :      74426  326694570          0      77489  326846485

vfsio_per_db

This script tracks the database I/O through the VFS layer. It also reports the I/O in this layer for non oracle database.

Usage:

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

Output example:

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

------------------------------------------------------------------------
DBNAME      : NB_READ   READ_KB   NB_WRITE  WRITE_KB  NB_TOTAL  TOTAL_KB
------------------------------------------------------------------------
BDTS        : 110       347       6         96        116       443
NOT_A_DB    : 89        11        2         0         91        11

blkio_per_db

This script tracks the database I/O through the block IO layer. It also reports the I/O in this layer for non oracle database.

Usage:

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

Output example:

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

------------------------------------------------------------------------
DBNAME      : NB_READ   READ_KB   NB_WRITE  WRITE_KB  NB_TOTAL  TOTAL_KB
------------------------------------------------------------------------
BDTS        : 9690      110768    18        192       9708      110960
NOT_A_DB    : 0         0         6         560       6         560

Remarks

  • The schedtimes_per_db script is mainly inspired by this one (full credit goes to the authors).
  • Why is it  interesting to look at the vfs layer? Answers are in this awesome File System Latency series (see parts 1, 2, 3, 4 and 5) from Brendan Gregg.
  • 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.

Conclusion

The toolkit has been created and 3 new members are part of it. Expect from it to grow a lot.