System CPU Metrics Analysis (sysmetric-cpu.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Script Correction Required

The original script contains a syntax error on line 27. The corrected version is provided below with the duplicate table reference fixed.

What This Script Does

This script provides Oracle database functionality via the sysmetric-cpu.sql script.

The Script

rem psysev.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
-- Corrected query (original had duplicate table reference)
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

What This Script Does

The sysmetric-cpu.sql script analyzes historical CPU utilization metrics from Oracle’s Automatic Workload Repository (AWR). It extracts Host CPU Utilization percentages from system metric summaries, grouped by AWR snapshot and instance, providing insights into CPU performance trends over time.

Usage

SQL> @sysmetric-cpu.sql

No parameters required - The script analyzes all available AWR snapshots with CPU metrics.

Required Privileges

• SELECT privilege on DBA_HIST_SYSMETRIC_SUMMARY (requires diagnostic license)
• Usually requires DBA role or specific AWR access grants

Sample Output

System Wait Events CPU

 SNAP ID INST AWR_START       AWR_END         HOST_CPU_UTIL
-------- ---- --------------- --------------- -------------
   45231   1 2024-06-04 14:00 2024-06-04 15:00         78.5
   45230   1 2024-06-04 13:00 2024-06-04 14:00         65.2
   45229   1 2024-06-04 12:00 2024-06-04 13:00         82.1
   45228   1 2024-06-04 11:00 2024-06-04 12:00         45.8
   45227   1 2024-06-04 10:00 2024-06-04 11:00         38.9

Key Output Columns

• SNAP ID: AWR snapshot identifier (newer snapshots have higher IDs)
• INST: Instance number (relevant for RAC environments)
• AWR_START: Beginning timestamp of the snapshot interval
• AWR_END: Ending timestamp of the snapshot interval
• HOST_CPU_UTIL: Average CPU utilization percentage during the interval

Understanding CPU Metrics

Normal CPU Ranges

• 0-50%: Light CPU usage, system has plenty of capacity
• 50-75%: Moderate usage, acceptable for most workloads
• 75-90%: High usage, may indicate performance pressure
• 90%+: Very high usage, potential performance bottleneck

RAC Environment Considerations

• Multiple rows per snapshot ID (one per instance)
• Compare CPU usage across instances for load balancing analysis
• Identify instances with consistently higher CPU utilization

Common Use Cases

1. Performance Trend Analysis

   • Track CPU utilization patterns over time
   • Identify peak usage periods and trends

2. Capacity Planning

   • Analyze historical CPU consumption for future capacity needs
   • Identify growth patterns and usage spikes

3. Performance Troubleshooting

   • Correlate high CPU periods with performance issues
   • Identify time periods requiring deeper analysis

4. Baseline Establishment

   • Establish normal CPU utilization baselines
   • Set thresholds for monitoring and alerting

Performance Analysis

Trend Identification

• Increasing Trend: May indicate growing workload or inefficient queries
• Spike Patterns: Could indicate batch jobs or specific application behaviors
• Sustained High Usage: May require performance tuning or hardware upgrades

Correlation Opportunities

Combine with other AWR data for comprehensive analysis:

• Top SQL statements during high CPU periods
• Wait events during peak CPU utilization
• I/O patterns correlating with CPU spikes

Troubleshooting Guide

High CPU Utilization Investigation

1. Identify Peak Periods:

   SELECT snap_id, awr_start, host_cpu_util
   FROM (query_results)
   WHERE host_cpu_util > 80
   ORDER BY host_cpu_util DESC;

2. Find Top SQL During Peak:

   SELECT sql_id, cpu_time_delta
   FROM dba_hist_sqlstat
   WHERE snap_id = [peak_snap_id]
   ORDER BY cpu_time_delta DESC;

Data Availability Issues

• No Output: Verify AWR snapshots exist and retention settings
• Limited History: Check AWR retention period configuration
• Missing Metrics: Ensure system metric collection is enabled

Script Modifications

Extended Time Range Analysis

-- Add date filtering for specific periods
WHERE begin_time >= DATE'2024-06-01'
  AND end_time <= DATE'2024-06-30'

Instance-Specific Analysis

-- Focus on specific instance in RAC
WHERE instance_number = 1

Additional Metrics

-- Include other CPU-related metrics
sum(case metric_name when 'Database CPU Time Per Sec' then average end) DB_CPU_time,
sum(case metric_name when 'CPU Usage Per Sec' then average end) CPU_usage_sec

Related Scripts

• awrrpt.sql - Complete AWR report generation
• gvcpu.sql - Real-time CPU analysis
• systime.sql - System-wide timing analysis

Script Features

• Historical Analysis: Leverages AWR data for trend analysis
• Multi-Instance Support: Handles RAC environments appropriately
• Time-based Grouping: Organizes data by snapshot intervals
• Descending Order: Shows most recent data first

Best Practices

1. Regular Monitoring: Review CPU trends weekly or monthly
2. Threshold Setting: Establish CPU utilization thresholds for your environment
3. Correlation Analysis: Combine with other performance metrics for complete picture
4. Documentation: Track CPU trends and their business impact

Limitations

• Requires Oracle Diagnostic Pack license for AWR access
• Limited to snapshot intervals (typically hourly)
• Historical data only (not real-time)
• Dependent on AWR retention settings for data availability