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.
