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

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
exec DBMS_LOCK.SLEEP (5);
set termout on
prompt &&s1,&&v1
undefine sleeptime
exit

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


#!/bin/bash
# ——————–
# Script : vosstat.sh
# ——————–
export ORACLE_HOME=/opt/app/oracle/product/11.1.0/db_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME:$PATH
export ORACLE_SID=db11FS
c=0
while [ “$c” -lt “10” ]
do
sqlplus -s / as sysdba @vosstat.sql
((c=c+1))
done

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,.139648438
LOAD,.129882813
LOAD,.119140625
LOAD,.219726563
LOAD, .19921875
LOAD,.439453125
LOAD,.399414063
LOAD,.369140625
LOAD,.419921875
LOAD,.459960938
LOAD,1.46972656

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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles