Skip to content
Oracle DBA Scripts & Database Utilities

System Performance Metrics Summary (system_stats.sql)

What This Script Does

Section titled “What This Script Does”

This script provides a comprehensive view of system performance metrics from AWR history, including:

  • Physical I/O rates (bytes/sec and IOPS)
  • Redo generation rates
  • CPU utilization (database and host)
  • Operating system load
  • Network traffic volume
  • All metrics aggregated by AWR snapshot

The Script

Section titled “The Script”
set lines 600
set pages 9999
spool sysmetric_outp.log


alter session set nls_date_format='dd-mm-yyyy hh24:mi';


select min(begin_time), max(end_time),
       sum(case metric_name when 'Physical Read Total Bytes Per Sec' then average end) Physical_Read_Total_Bps,
       sum(case metric_name when 'Physical Write Total Bytes Per Sec' then average end) Physical_Write_Total_Bps,
       sum(case metric_name when 'Redo Generated Per Sec' then average end) Redo_Bytes_per_sec,
       sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then average end) Physical_Read_IOPS,
       sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end) Physical_write_IOPS,
       sum(case metric_name when 'Redo Writes Per Sec' then average end) Physical_redo_IOPS,
       sum(case metric_name when 'Current OS Load' then average end) OS_LOad,
       sum(case metric_name when 'CPU Usage Per Sec' then average end) DB_CPU_Usage_per_sec,
       sum(case metric_name when 'Host CPU Utilization (%)' then average end) Host_CPU_util, --NOTE 100% = 1 loaded RAC node
       sum(case metric_name when 'Network Traffic Volume Per Sec' then average end) Network_bytes_per_sec,
       snap_id
from dba_hist_sysmetric_summary
where snap_id > &starting_snap_id
group by snap_id
order by snap_id;


spool off

Usage

Section titled “Usage”
-- Basic usage
@system_stats.sql


-- When prompted, enter:
-- starting_snap_id: Starting snapshot ID (e.g., 36595)

Parameters

Section titled “Parameters”

The script prompts for:

  • &starting_snap_id - AWR snapshot ID to start from

Required Privileges

Section titled “Required Privileges”
SELECT ON DBA_HIST_SYSMETRIC_SUMMARY

Sample Output

Section titled “Sample Output”
MIN(BEGIN_TIME)    MAX(END_TIME)      PHYSICAL_READ_TOTAL_BPS PHYSICAL_WRITE_TOTAL_BPS REDO_BYTES_PER_SEC PHYSICAL_READ_IOPS PHYSICAL_WRITE_IOPS PHYSICAL_REDO_IOPS OS_LOAD DB_CPU_USAGE_PER_SEC HOST_CPU_UTIL NETWORK_BYTES_PER_SEC   SNAP_ID
------------------ ------------------ ----------------------- ------------------------ ------------------ ------------------- -------------------- ------------------ ------- -------------------- ------------- --------------------- ---------
06-12-2024 10:00   06-12-2024 11:00                 10485760                  5242880            1048576                 125                   65                 15     2.5                 85.5          12.5               524288     36596
06-12-2024 11:00   06-12-2024 12:00                 15728640                  8388608            2097152                 189                   98                 22     3.8                125.2          18.7               786432     36597
06-12-2024 12:00   06-12-2024 13:00                 52428800                 26214400            5242880                 625                  312                 45     8.2                245.8          45.2              1572864     36598
06-12-2024 13:00   06-12-2024 14:00                 31457280                 15728640            3145728                 376                  188                 32     5.1                156.3          28.9              1048576     36599

Key Output Columns

Section titled “Key Output Columns”
  • MIN/MAX TIME - Snapshot interval start and end
  • PHYSICAL_READ_TOTAL_BPS - Physical read bytes per second
  • PHYSICAL_WRITE_TOTAL_BPS - Physical write bytes per second
  • REDO_BYTES_PER_SEC - Redo generation rate
  • PHYSICAL_READ_IOPS - Read I/O operations per second
  • PHYSICAL_WRITE_IOPS - Write I/O operations per second
  • PHYSICAL_REDO_IOPS - Redo write operations per second
  • OS_LOAD - Operating system load average
  • DB_CPU_USAGE_PER_SEC - Database CPU centiseconds per second
  • HOST_CPU_UTIL - Host CPU utilization percentage
  • NETWORK_BYTES_PER_SEC - Network traffic rate
  • SNAP_ID - AWR snapshot identifier

Common Use Cases

Section titled “Common Use Cases”

Capacity Planning

@system_stats.sql
-- Enter recent snap_id
-- Analyze trends in resource usage

Performance Baseline

@system_stats.sql
-- Capture during normal operations
-- Compare during performance issues

Storage I/O Analysis

@system_stats.sql
-- Focus on IOPS and bytes/sec columns
-- Identify I/O bottlenecks

Performance Analysis Tips

Section titled “Performance Analysis Tips”

I/O Metrics:

  • Compare read vs write ratios
  • High IOPS with low bytes/sec = small random I/O
  • Low IOPS with high bytes/sec = large sequential I/O

CPU Metrics:

  • DB_CPU > number of CPUs = CPU bottleneck
  • Host CPU includes all processes, not just database
  • OS Load > CPU count indicates CPU pressure

Redo Metrics:

  • High redo generation = heavy DML activity
  • Redo IOPS indicates log writer activity
Section titled “Related Scripts”