Top Oracle Wait Events from ASH in the Last Hour

Extracts the busiest wait events and SQL from Active Session History over the last hour. Used for retrospective performance analysis.

#awr#wait-events#ash

oracle · sql

SELECT NVL(event, 'ON CPU') AS event, wait_class,
       COUNT(*) AS ash_samples,
       ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) AS pct
FROM   v$active_session_history
WHERE  sample_time > SYSDATE - 1/24
GROUP  BY event, wait_class
ORDER  BY ash_samples DESC
FETCH FIRST 15 ROWS ONLY;

Operational Notes & Remediation Guidance

Active Session History (ASH) provides second-by-second visibility into session bottlenecks without trace overhead.

Production Safety & Execution Standards

This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
Prior to executing on production systems under high load, verify the target database version (Min. Version: 11g).
Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.

Other ORACLE Diagnostic Scripts

View All (100)

#61Performance

Oracle Active Sessions and Wait Events

Lists active user sessions with the running SQL_ID and wait event. Shows the live picture during performance issues.

View Script

#62Locking

Find Blocking Sessions in Oracle

Shows sessions that make others wait, with wait times. Identifies the root session to terminate in lock incidents.

View Script

#63Storage

Oracle Tablespace Usage Including Autoextend

Calculates tablespace usage against the real maximum, honoring autoextend limits. Catches space errors like ORA-01653 before they happen.

View Script

#64Performance

Top 20 Most Expensive Oracle SQL with v$sqlstats

Shows costliest SQL by elapsed time with executions, CPU and I/O. Tells you which SQL_ID to investigate for tuning.

View Script