Where Does v$osstat Get It's Data? Trustworthy?

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

OS Data Without An OS Prompt

Have you ever wanted to get OS data from within Oracle Database 12c/11g/10g and without being at the OS prompt? Have you ever wondered where v$osstat gets it's data?

I have! It's the kind of stuff I think about all the time. In fact, I was so fascinated by what I found, I included the details in my online seminar, Utilization On Steroids. It's that cool.

What Is So Great About v$osstat?

For starters, v$osstat provides an operating system perspective view of OS activity. In contrast, v$sysstat or v$sys_time_model provide performance data about a specific instance. Looking at v$osstat data is like looking at OS data from within Oracle. In other words, seeing OS data without an OS prompt.

But is the data from v$osstat trustworthy? That's exactly what this posting is all about. To figure this out, I'm going to first look at where vmstat gets its data and then look at where v$osstat gets its data. If their data source is the same, then it is highly likely v$osstat is trustworthy. Make sense? Read on...

Where Does vmstat Get It's Data?

It's real simple to determine vmstat's data source. Just OS trace vmstat! On Linux I use the strace command. In the exert below, I did not change any lines. If I removed lines, I replaced them a "...". Here is the output:

[oracle@sixcore local]$ strace vmstat 2 5
execve("/usr/bin/vmstat", ["vmstat", "2", "5"], [/* 39 vars */]) = 0
...
write(1, "procs -----------memory---------"..., 81procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
) = 81
write(1, " r  b   swpd   free   buff  cach"..., 81 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
) = 81
open("/proc/meminfo", O_RDONLY)         = 3
lseek(3, 0, SEEK_SET)                   = 0
read(3, "MemTotal:       24729532 kB\nMemF"..., 2047) = 1198
open("/proc/stat", O_RDONLY)            = 4
read(4, "cpu  570795858 11695 216600983 8"..., 65535) = 4643
open("/proc/vmstat", O_RDONLY)          = 5
lseek(5, 0, SEEK_SET)                   = 0
read(5, "nr_free_pages 119649\nnr_inactive"..., 2047) = 2047
write(1, " 2  0 146388 478596 317080 22558"..., 84 2  0 146388 478596 317080 22558284    0    0     5   234    1    4 72 27  0  0  0 
) = 84
...
nanosleep({2, 0}, 0x7fff0a9313b0)       = 0
...
lseek(3, 0, SEEK_SET)                   = 0
read(3, "MemTotal:       24729532 kB\nMemF"..., 2047) = 1198
lseek(4, 0, SEEK_SET)                   = 0
read(4, "cpu  570796318 11695 216600996 8"..., 65535) = 4643
lseek(5, 0, SEEK_SET)                   = 0
read(5, "nr_free_pages 119672\nnr_inactive"..., 2047) = 2047
write(1, " 2  0 146388 478688 317080 22558"..., 84 2  0 146388 478688 317080 22558284    0    0     0    36 3781 2311 38  1 61  0  0 
) = 84
...
nanosleep({2, 0}, 
...
exit_group(0)                           = ?
[oracle@sixcore local]$ 

There are two things I want to bring to your attention. First is the nonosleep call. If you do a man page on nanosleep, you can see that vmstat is using the call for the two second sleep between data output.

The second and the most important for this posting, is vmstat repeatedly references data in the /proc filesystem. I write about the /proc filesystem in my Oracle Performance Firefighting book and also explore it in my Utilization On Steroids online seminar. It's amazing. The bottom line is this, vmstat gets performance data from the /proc filesystem. Place that in your stack and read on.

Where Does v$osstat Get Its Data?

The Oracle Database background process mmnl, at least in part, is responsible for collecting data that we see in v$osstat. So I am going to OS trace using strace the mmnl process and see what files it opens and reads!

First I need to get the OS process ID of the mmnl background process for my Oracle Database 12c instance.

[oracle@sixcore local]$ ps -eaf|grep mmnl
oracle   11521 11170  0 08:59 pts/1    00:00:00 grep mmnl
oracle   20109     1  0 Feb24 ?        00:18:47 ora_mmnl_prod35
oracle   60274     1  0 Jan13 ?        00:24:34 ora_mmnl_prod30

Got it! The PID for my Oracle Database 12c instance is 20109. Just like I did with vmstat, except mmnl is already running, I'm going to OS trace it using strace.  In the exert below, I did not change any lines. If I removed lines, I replaced them a "...". Here is the output:

[oracle@sixcore local]$ strace -p 20109
Process 20109 attached - interrupt to quit
...
open("/proc/cpuinfo", O_RDONLY)         = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "processor\t: 0\nvendor_id\t: Genuin"..., 1024) = 1024
read(15, " size\t: 12288 KB\nphysical id\t: 0"..., 1024) = 1024
read(15, "gs\t\t: fpu vme de pse tsc msr pae"..., 1024) = 1024
read(15, "_tsc arch_perfmon pebs bts rep_g"..., 1024) = 1024
read(15, "2 popcnt aes lahf_lm ida arat ep"..., 1024) = 1024
read(15, "ical, 48 bits virtual\npower mana"..., 1024) = 41
read(15, "", 1024)                      = 0
open("/proc/mounts", O_RDONLY)          = 16
fstat(16, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bc000
read(16, "rootfs / rootfs rw 0 0\nproc /pro"..., 1024) = 886
read(16, "", 1024)                      = 0
close(16)                               = 0
munmap(0x7f68548bc000, 4096)            = 0
close(15)                               = 0
munmap(0x7f68548bd000, 4096)            = 0
open("/proc/loadavg", O_RDONLY)         = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "2.11 2.07 1.73 5/302 11525\n", 1024) = 27
close(15)                               = 0
munmap(0x7f68548bd000, 4096)            = 0
open("/proc/stat", O_RDONLY)            = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "cpu  571012017 11695 216602922 8"..., 1024) = 1024
close(15)                               = 0
munmap(0x7f68548bd000, 4096)            = 0
open("/proc/meminfo", O_RDONLY)         = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "MemTotal:       24729532 kB\nMemF"..., 1024) = 1024
close(15)                               = 0
munmap(0x7f68548bd000, 4096)            = 0
open("/proc/vmstat", O_RDONLY)          = 15
fstat(15, {st_mode=S_IFREG|0444, st_size=0, ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x7f68548bd000
read(15, "nr_free_pages 119496\nnr_inactive"..., 1024) = 1024
close(15)  
...
semtimedop(5341187, {{24, -1, 0}}, 1, {1, 0}^C 
Process 20109 detached
[oracle@sixcore local]$ 

Just in the few lines above you can see that the mmnl background process opens and reads data from /proc/cpuinfo, /proc/mounts, /proc/loadavg, /proc/stat, /proc/meminfo, and /proc/vmstat. While a bit scary if you are violating your Oracle Corporation licensing agreement, this is truly fascinating!

While my demonstration does not prove this and I have never seen or heard anything to the contrary, it does appear the source of OS data for v$osstat is the /proc filesystem. And certainly, the Oracle Database background process mmnl repeatedly references the /proc filesystem.

Therefore, if the source of OS data is the same for both vmstat and v$osstat, I will trust the data from v$osstat unless there is a really good reason to not trust the data.

What Can I Do With v$osstat?

That's a great question, but not the focus of this posting. But just to give you some ideas, we can easily determine OS CPU utilization solely with $osstat, an AWR report, or a Statspack report. If fact, next week I'm going to give a 30 minute Quick Tip at the 2014 IOUG/Collaborate conference entitled, Calculating OS CPU Utilization From V$ Views, AWR, and Statspack. OK... here is the link where you can download the slide deck for my most recent version of the conference presentation.

Utilization is a fascinating topic. It is far reaching and touches on all things computing and even beyond. And as you might expect, I really get into this in my online seminar entitle, Utilization On Steroids. It's the simplest concepts that are the most profound.

Thanks for reading!

Craig.