System CPU Metrics (SYSMETRIC_CPU.sql)

What This Script Does

This script analyzes historical CPU utilization from AWR system metrics, providing a snapshot-based view of host CPU utilization across all instances. Essential for historical performance analysis and capacity planning.

The Script

rem sysmetric_cpu.sql
rem
set lines 600
rem
alter session set nls_date_format='YYYY-MM-DD HH24:Mi'
/
col SNAP_ID format 999999 heading 'SNAP ID'
col instance_number format 99 head 'INST'
col end_interval_time format a15 heading 'SNAP TIME'
col EVENT_NAME format a35 heading 'EVENT'
col total_waits format b999,999,990 heading 'TOTAL WAITS'
col waits_per_sec format b99,990 heading 'WAITS|PER|SEC'
col timeout_pct format b990.0 heading 'TIMEOUT|PCT'
col time_waited_micro format b999,999,990 heading 'TIME WAITED(ms)'
col avg_wait format 990.0 heading 'AVG|WAIT|TIME(ms)'
rem
rem
ttitle 'System Wait Events CPU'
rem
clear break
clear compute
rem
select        snap_id, INSTANCE_NUMBER,
min(begin_time) awr_start, max(end_time) awr_end,
       sum(case metric_name when 'Host CPU Utilization (%)' then average end) Host_CPU_util
from dba_hist_sysmetric_summary
group by snap_id , instance_number
order by snap_id desc, INSTANCE_NUMBER;
rem
undefine start_dt
undefine end_dt
undefine nam
undefine instno

Parameters

The script prompts for:

None - this script requires no parameters

Usage

-- Basic usage
@SYSMETRIC_CPU.sql

Sample Output

                               System Wait Events CPU

SNAP ID INST AWR_START           AWR_END             HOST_CPU_UTIL
------- ---- ------------------- ------------------- -------------
  12345   1  2025-01-05 14:00:00 2025-01-05 15:00:00         45.2
  12345   2  2025-01-05 14:00:00 2025-01-05 15:00:00         52.8
  12344   1  2025-01-05 13:00:00 2025-01-05 14:00:00         38.9
  12344   2  2025-01-05 13:00:00 2025-01-05 14:00:00         41.7
  12343   1  2025-01-05 12:00:00 2025-01-05 13:00:00         35.1
  12343   2  2025-01-05 12:00:00 2025-01-05 13:00:00         37.4