Skip to content

v$osstat contains operating system statistics – knowledge article for the new oracle dba




you are a new oracle dba and you would like to know about some oracle internal views which can show you some operating system level statistics like the virtual memory , cpu utilisation etc. Oracle has a nice view called v$OSSTAT that gives you this info. Please read the below discussion to get an overview of some parameters like Virtual Memory and Paging in and paging out.

 

What is VM_OUT_BYTES?

Normally you would look at VM_OUT_BYTES and say that the O/S was trying to preserve the content of part of the memory by writing it to disk (temporarily) so that it could read it back later, and this would be an indication that your configuration was demanding more memory that was really available.

 

Your arithmetic shows that this excess is enormous – do the figures look realistic to you ? Has your machine (o/s) been live that long, have you seen anything that looks remotely like 6GB per second (in both directions) going on.

 

Given your later comment about vmware, it’s possible that you’re seeing some sort of vmware glitch. Statspack and AWR both report OS Stats in 10g, and their reports are based on differences across the time period: even if the absolute numbers have managed to pick up an enormous overhead it’s possible that for most of the time the deltas will show something reasonable.

 

Another sanity check – you could query the values every 10 seconds (pl/sql loop and store to table) and see if the numbers are stable for a while and then do an impossible jump

 

From some document on oracle website

 

–           stat_name = VM_IN_BYTES.   The value should correspond to the total number of bytes paged in due to virtual memory paging.  The number of bytes paged in during the past minute can be obtained by querying v$osstat at a one minute interval.  The number of bytes paged in during the past minute should be equivalent to the per-second rate given by “sar –B 60” under the statistic “pgpgin/s”.

–           stat_name = VM_OUT_BYTES.   The value should correspond to the total number of bytes paged out due to virtual memory paging.  The number of bytes paged out during the past minute can be obtained by querying v$osstat at a one minute interval.  The number of bytes paged out during the past minute should be equivalent to the per-second rate given by “sar –B 60” under the statistic “pgpgout/s”.

 

HI,
the parameters are referred to the Virtual Memory (not Virtual Machine).
I’ve saw similar behaviour migrating a 10g r2 32Bit W2K to 10g r2 64Bit Win 2003.
I don’t kwno why but, in Win 2003 env, most of the SGA stays in the virtal memory instead of the real one. I’ve tested the LOCK_SGA parameter but It doesn’t works in Win 2xxx envs.
Anyway, it’s Yr migratin like mine or the migration is from, for ex. 9i r2 32 to 10g r2 64?
I don’t know Yr skill, so forgive me if I ask some simple question, but are the object statistics updated after the DB migration? Are the instance parameters suitables for the new environement? The execution plans have changed from old to new instance?

maxcastagno | Sat, 23 Feb 200813:41:00 GMT |

Hi Max
I pretty much convinced myself VM is virtual memory. We’re not running a migration as such – we have data exports from two schemas and are “merging” the two into one schema. It’s the merge which is causing the problem. We can tune the query easily – for instance it is now a single threaded process and does not use parallel – so we do have some CPU issues – but the performance problem has uncovered a fundamental flaw in our assumption that 64 bit will work faster than 32 bit! I think there are some hardware config issues here as well (page file size) as well as Oracle instance tuning required (SGA size) and a bit of configuration to complement each other!.
Thanks for your thoughts and pointers though.
Dave

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

By James Koopmann

 

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.

what is the new Oracle view v$osstat?

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.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.