OracleASH & AWR

Top SQL in the last hour (ASH)

Statements with the most sampled activity in the last hour, split into CPU, user I/O and other waits. Each sample is roughly one second of database time.

Diagnostics Pack

Not yet verified. How scripts are tested

ora-ash-sql.sql

1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN sql_id     FORMAT A134COLUMN samples    HEADING "DB SECS"  FORMAT 999,9905COLUMN pct        HEADING "% ACTIVE" FORMAT 990.96COLUMN on_cpu     HEADING "CPU"      FORMAT 999,9907COLUMN user_io    HEADING "USER I/O" FORMAT 999,9908COLUMN other_wait HEADING "OTHER"    FORMAT 999,9909 10SELECT * FROM (11  SELECT sql_id,12         COUNT(*) AS samples,13         ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) AS pct,14         SUM(CASE WHEN session_state = 'ON CPU' THEN 1 ELSE 0 END) AS on_cpu,15         SUM(CASE WHEN wait_class = 'User I/O' THEN 1 ELSE 0 END) AS user_io,16         SUM(CASE WHEN session_state = 'WAITING' AND wait_class <> 'User I/O' THEN 1 ELSE 0 END) AS other_wait17  FROM v$active_session_history18  WHERE sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR19    AND sql_id IS NOT NULL20  GROUP BY sql_id21  ORDER BY samples DESC22) WHERE ROWNUM <= 15;

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

Open in denrepo

More Oracle scripts: ASH & AWR

Run an AWR report (one instance or all)Oracle's AWR report scripts. The first reports on the instance you're connected to; the second is the global report across all RAC instances. Each…
Top wait events in the last hour (ASH)Where the database spent its time over the last hour, with CPU counted as its own line.
What was running between two times (AWR ASH)For "it was slow at 2 a.m." questions. Prompts for a start and end time (YYYY-MM-DD HH24:MI) and ranks SQL and events from AWR's history, where each…
AWR settings and recent snapshotsSnapshot interval and retention, then every snapshot from the last 24 hours. You need the snapshot IDs to run an AWR report.
Generate AWR, ASH, ADDM and SQL reportsOracle's own report scripts. Each one prompts for the format, snapshot range and file name, then writes the report to your current directory.
Take an AWR snapshot nowTakes a manual snapshot so an AWR report can cover exactly the window of a test or a batch run. Take one before and one after.