OraclePerformance

Top wait events since startup

The 15 non-idle waits with the most total time since the instance started, with average wait in milliseconds. Averages matter: 'db file sequential read' over 10 ms suggests slow storage.

Not yet verified. How scripts are tested

1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN event       FORMAT A454COLUMN wait_class  HEADING "WAIT CLASS" FORMAT A155COLUMN total_waits HEADING "WAITS"      FORMAT 999,999,999,9906COLUMN waited_s    HEADING "TOTAL S"    FORMAT 999,999,9907COLUMN avg_ms      HEADING "AVG MS"     FORMAT 999,990.998 9SELECT * FROM (10  SELECT event, wait_class, total_waits,11         ROUND(time_waited_micro / 1e6) AS waited_s,12         ROUND(time_waited_micro / NULLIF(total_waits, 0) / 1000, 2) AS avg_ms13  FROM v$system_event14  WHERE wait_class <> 'Idle'15  ORDER BY time_waited_micro DESC16) WHERE ROWNUM <= 15;

Save it as ora-system-waits.sql and run it with SQL> @ora-system-waits.

Open in denrepo

More Oracle scripts: Performance

• Load right now (last 60 seconds)Host CPU, average active sessions, transactions, redo and I/O rates from V$SYSMETRIC. Compare average active sessions with your CPU count to judge…
• What active sessions are doing right nowActive user sessions grouped by wait event, with CPU shown separately. A quick live picture before you reach for ASH.
• Top 20 SQL by elapsed timeFrom V$SQLSTATS, which is cheaper to query than V$SQL. Feed the SQL_ID into the execution plan script to see how it runs.
• Top sessions by CPU usedConnected sessions ranked by total CPU seconds since they logged on. Good for finding the one batch job or report hammering the box.