Question: I use your STATSPACK extension
table for vmstat (stats$vmstat)
and I have been quite happy with my IS stats. I now understand
that Oracle offers a view with limited OS data, called v$osstat.
Is v$osstat better than a custom OS statistics collection?
Answer: Traditionally, Oracle ran in a
vacuum, and you had to go "outside" of Oracle to measure CPU, RAM,
Network and disk spindle I/O bottlenecks. However, Oracle has
now advanced and an instance samples its external environment
placing OS data into v$osstat, dba_hist_system_event.
The v$osstat is created by querying the
gv$osstat view, slicing-off data for each specific instance on
the server:
select
stat_name,
value,
osstat_id,
comments,
cumulative
from
gv$osstat
where
inst_id = userenv('instance')
Oracle has several views that collect OS information, namely
v$osstat, stats$osstat, stats$osstatname and
dba_hist_osstat.
Still, many DBAs will write custom scripts to gather
OS statistics, collecting from OS tools such as vmstat and
placing the data into STATSPACK extension tables for trend analysis.
Querying from v$osstat ensures that an Oracle
performance problem is not constrained by an external OS
bottleneck.
select * from v$osstat;
select * from
dba_hist_osstat;
select * from dba_hist_system_event;
-- If you don't want to pay the fee to purchase
AWR, use my vmstat table
select * from stats$vmstat
