AboutHow 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.