Oracle knows that the database does not run in a vacuum and that external environmental events have a huge impact on Oracle performance.

The speed of disk access, the backlog of CPU enqueues, network latency and RAM swapping can all be the root cause of an Oracle performance problem, and no amount of tuning can fix a problem that is at the OS layer, external to Oracle.

Oracle has two main ways to collect OS information:

• dbms_stats - the gather_system_stats procedure samples important OS metrics.

• v$ Views - Oracle background processes also collect OS information and  store them in v$osstat.  In STATSPACK we have OS information in stats$osstat and  stats$osstatname and in AWR we have OS statistics inside the dba_hist_osstat table.

Let's take a closer look at the operating system statistics captured by Oracle.

Using dbms_stats.gather_system_stats

The dbms_stats.gather_system_stats procedure measures important timings within the database and adjusts the optimizers propensity to choose indexes vs. full-scans.  Oracle captures these OS statistics:

Here are the data items collected by dbms_stats.gather_system_stats:

Non Workload (NW) OS statistics:

• CPUSPEEDNW - CPU speed
• IOSEEKTIM - The I/O seek time in milliseconds
• IOTFRSPEED - I/O transfer speed in milliseconds

Workload-related OS statistics:

• SREADTIM  - Single block read time in milliseconds
• MREADTIM - Multiblock read time in ms
• CPUSPEED - CPU speed
• MBRC - Average blocks read per multiblock read
• MAXTHR - Maximum I/O throughput (for OPQ only)
• SLAVETHR - OPQ Factotum (slave) throughput (OPQ only)   

Here is a script to display these Oracle OS statistics:

select
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
   (
      sum(a.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c3,
   (
      sum(b.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c4,
  (
      sum(b.time_waited_micro) /
      sum(b.total_waits)) /
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5
from
   dba_hist_system_event a,
SEE CODE DEPOT FOR FULL SCRIPTS
   dba_hist_system_event b
where
   a.snap_id = b.snap_id
and
   a.event_name = 'db file scattered read'
and
   b.event_name = 'db file sequential read';

OS data inside Oracle views

Oracle has several views that collect OS information, namely v$osstat, stats$osstat, stats$osstatname and dba_hist_osstat.

Here is a script that queries the AWR for OS statistics:

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.