Looking at your operating system with the help of Oracle's V$OSSTAT view

Thursday Feb 5th 2009 by James Koopmann

James Koopmann provides a quick overview of the V$OSSTAT view and how it can help us gain some information about the operating system our Oracle database is on.

How often have we been told to take a look at our operating system documentation to find tuning information about the systems our Oracle databases reside on? I honestly get a little tired of that statement and for years have wanted to be able to get some operating system information from within Oracle. It seems like an easy thing to do. After all Oracle is a powerful application that has access to much of the system.

This article takes a quick look at the V$OSSTAT view and how it can help us gain some information about the operating system our Oracle database is on. The V$OSSTAT view will, as the Oracle manual states, display system utilization statistics from the operating system, one row for each system statistic. Well, I’ll be the first to tell you that this view doesn’t display a row for EVERY system statistic, this would be a bit ridiculous as the operating system has utilities such as sar, iostat, vmstat, etc. Nevertheless, let’s take a look since there is some interesting information.

For the V$OSSTAT view there are the following columns:

STAT_NAME—the name of a selected number of statistics Oracle wants to keep track of through this particular view.

VALUE—self-explanatory, but is the value for the statistic at that point in time.

COMMENTS—a nice description of the statistic along with information on the VALUE column such as size, time interval, etc.

CUMULATIVE—lets us know if the VALUE is cumulative over time. Basically, all of the statistics with a time value are cumulative while the others are a value at the instance in time when the query against this view was performed.

For the V$OSSTAT view, some of the more interesting statistics end up being the following.

STAT_NAME                 COMMENTS
------------------------- ------------------------------------------------------------
NUM_CPUS                  Number of active CPUs
IDLE_TIME                 Time (centi-secs) that CPUs have been in the idle state
BUSY_TIME                 Time (centi-secs) that CPUs have been in the busy state
USER_TIME                 Time (centi-secs) spent in user code
SYS_TIME                  Time (centi-secs) spent in the kernel
IOWAIT_TIME               Time (centi-secs) spent waiting for IO
NICE_TIME                 Time (centi-secs) spend in low-priority user code
RSRC_MGR_CPU_WAIT_TIME    Time (centi-secs) processes spent in the runnable state waiting
LOAD                      Number of processes running or waiting on the run queue
PHYSICAL_MEMORY_BYTES     Physical memory size in bytes

Now, depending on your particular operating system there may be more or less statistics that the V$OSSTAT view reports, monitors, and accumulates on. For instance, there are statistics on some virtual paging, and CPU cores and sockets. You would have to just query the V$OSSTAT view to see what is available on your particular instance. Just remember that you can’t rely on one statistic to be there just because it’s on another instance, unless of course they have the same configuration. For simplicity, use the following SQL:

SELECT * FROM v$osstat;

Now when I always get information like this from Oracle, I tend to want it displayed in the standard Unix like output. Much like iostat would be displayed. Therefore, I’ve created a shell script and a SQL statement that can be used from the operating system prompt. The shell script will issue a SQLPlus command with a call to the SQL script. Give it a try, change the sleep and iteration counts or change the statistic you want to report on. Do this and you will have a nice way to continually watch some operating system statistics from within Oracle.

--# ---------------------
--# Script  : vosstat.sql
--# ---------------------
set echo     off
set feedback off
set heading  off
set linesize 40
set pagesize 55
set verify   off
column s1 new_value s1
column v1 new_value v1
set termout off
select stat_name s1,
       value     v1
  from v$osstat
 where stat_name = 'LOAD';
column sleeptime  new_value sleeptime
set termout on
prompt &&s1,&&v1
undefine sleeptime

Then just use the following shell script to call the vosstat.sql script.

# --------------------
# Script  : vosstat.sh
# --------------------
export ORACLE_HOME=/opt/app/oracle/product/11.1.0/db_1
export ORACLE_SID=db11FS
while [ "$c" -lt "10" ]
  sqlplus -s / as sysdba @vosstat.sql

What you will end up getting is something like the following (a load displayed every 5 seconds for 10 iterations):

[oracle@ludwig ~]$ ./vosstat.sh
LOAD, .19921875

Now, watching something as it happens is very beneficial. You can often catch things while they are happening. However, if you happen to not be at the terminal, watching every move your instance is making, there is always the historical V$OSSTAT view called DBA_HIST_OSSTAT. This contains much of the same information except it is done in conjunction with snapshots. Since it is done with snapshots you don’t get that real-time feel for what is happening, but you can always take a look by issuing the following SQL:

SQL> SELECT snap_id, dbid, stat_name, value
       FROM dba_hist_osstat
      WHERE stat_name = 'LOAD'
      ORDER BY snap_id;
   SNAP_ID       DBID STAT_NAME                      VALUE
---------- ---------- ------------------------- ----------
        25 1291685745 LOAD                      .129882813
        26 1291685745 LOAD                      .209960938
        27 1291685745 LOAD                       .26953125

A simple use case always helps us prove that these new views in Oracle can be beneficial. Suppose, for example, that you think there is a bottleneck on the system. You could always use sar or vmstat but because the operating system level statistics are collected in the database now, you can just issue a SELECT statement against the V$OSSTAT view. By looking at the IDLE times (processor/CPU idle time) and comparing against the BUSY times (processor/CPU busy time) you can easily determine the percentage of time the CPU has been busy. If the busyness of the CPU is high enough, then you can easily conclude that in fact the CPU is a bottleneck.

Understanding operating system performance is difficult enough. When you have multiple operating system side utilities like sar and vmstat it can be difficult to answer some of the easier questions like CPU performance. With Oracle’s V$OSSTAT view we get the simplicity of having a view inside Oracle that quickly gets us to some pertinent statistics and provides us a mechanism to easily incorporate those statistics into a scripting solution for monitoring our databases.

» See All Articles by Columnist James Koopmann

Mobile Site | Full Site