Operating System Statistics in AWR
Operating system (OS) statistics such as CPU, disk
input/output (I/O), virtual memory, and network statistics
help identify possible bottlenecks where system hardware is
stressed.
The AWR has a view called
dba_hist_osstat
that stores snapshots of the
v$osstat dynamic view. OS statistics indicate how the hardware and
OS are working, and thus, they reflect the workload placed
on the database. These statistics can give an indication of
where to first search the database for possible hot spots.
The structure of
dba_hist_osstat
view is:
SQL> desc DBA_HIST_OSSTAT
Name Null? Type
----------------------------------------- --------
----------
SNAP_ID NUMBER
DBID NUMBER
INSTANCE_NUMBER NUMBER
STAT_ID NUMBER
STAT_NAME
VARCHAR2(64)
VALUE NUMBER
To view history statistics for a particular snapshot
interval, the
os_stat_int_10g.sql
query can be used:
select e.stat_name "Statistic Name"
, decode(e.stat_name, 'NUM_CPUS', e.value, e.value
- b.value) "Total"
, decode( instrb(e.stat_name, 'BYTES'), 0,
to_number(null)
, round((e.value - b.value)/( select
avg( extract( day from
(e1.end_interval_time-b1.end_interval_time) )*24*60*60+
extract( hour from
(e1.end_interval_time-b1.end_interval_time) )*60*60+
extract( minute from
(e1.end_interval_time-b1.end_interval_time) )*60+
extract( second from
(e1.end_interval_time-b1.end_interval_time)) )
from dba_hist_snapshot b1
,dba_hist_snapshot e1
where b1.snap_id = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid = b.dbid
and e1.dbid = e.dbid
and b1.instance_number = b.instance_number
and e1.instance_number = e.instance_number
and b1.startup_time = e1.startup_time
and b1.end_interval_time < e1.end_interval_time
),2)) "Per Second"
from dba_hist_osstat b
, dba_hist_osstat e
where b.snap_id = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid = &pDbId
SEE CODE DEPOT FOR FULL SCRIPTS
order by 1 asc;
The query output looks like the following:
SQL> @os_stat_int_10g.sql
Statistic Name Total Per Second
------------------------------ ---------- ----------
AVG_BUSY_TICKS 1,974,925
AVG_IDLE_TICKS 7,382,241
AVG_IN_BYTES 2,236,256,256 23,881.91
AVG_OUT_BYTES 566,304,768 6047.8
AVG_SYS_TICKS 727,533
AVG_USER_TICKS 1,247,392
BUSY_TICKS 1,974,925
IDLE_TICKS 7,382,241
IN_BYTES 2,236,256,256 23,881.91
NUM_CPUS 1
OUT_BYTES 566,304,768 6,047.8
SYS_TICKS 727,533
USER_TICKS 1247,392
The
os_stat_int_10g.sql
script allows a view of OS statistics in two forms:
cumulative and per second. Thus, users are able to identify
hot areas in the OS and hardware.
The Ion tool has a corresponding report called OS
Statistics that is used to produce history charts. Figure
4.14 is a representation of the results of the OS Statistics
from the AWR interval.
Figure 4.14:
AWR OS Statistics
chart in Ion
The above sample Ion screenshot displays a sample chart
produced by the OS Statistics report. This chart allows the
identification of times when operating system experienced
the most workload and overhead during the snapshot interval
observed.
The next section describes the very important
dba_hist views
that contain performance history information for SQL
statements executed in the Oracle10g database.
SEE CODE DEPOT FOR FULL SCRIPTS
and include the URL for the page.
