OracleASH & AWR
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.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN activity FORMAT A454COLUMN wait_class HEADING "WAIT CLASS" FORMAT A155COLUMN samples HEADING "DB SECS" FORMAT 999,9906COLUMN pct HEADING "%" FORMAT 990.97 8SELECT * FROM (9 SELECT NVL(event, 'ON CPU') AS activity,10 NVL(wait_class, 'CPU') AS wait_class,11 COUNT(*) AS samples,12 ROUND(100 * RATIO_TO_REPORT(COUNT(*)) OVER (), 1) AS pct13 FROM v$active_session_history14 WHERE sample_time > SYSTIMESTAMP - INTERVAL '1' HOUR15 GROUP BY NVL(event, 'ON CPU'), NVL(wait_class, 'CPU')16 ORDER BY samples DESC17) WHERE ROWNUM <= 15;Save it as ora-ash-events.sql and run it with SQL> @ora-ash-events.
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 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…
- 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.