| |
 |
|
Oracle Tips by Burleson |
Operating System Statistics
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 thus reflecting 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 the
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 below should be used:
* os_stat_int_10g.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
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
and e.dbid = &pDbId
and b.instance_number = &pInstNum
and e.instance_number = &pInstNum
and b.stat_id = e.stat_id
and e.value >= b.value
and e.value > 0
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 23881,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 23881,91
NUM_CPUS 1
OUT_BYTES 566,304,768 6047,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 above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
