OracleASH & AWR
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 sample represents about ten seconds.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF VERIFY OFF3COLUMN sql_id FORMAT A134COLUMN activity FORMAT A455COLUMN est_db_secs HEADING "EST DB SECS" FORMAT 999,999,9906 7SELECT * FROM (8 SELECT sql_id,9 NVL(event, 'ON CPU') AS activity,10 COUNT(*) * 10 AS est_db_secs11 FROM dba_hist_active_sess_history12 WHERE sample_time BETWEEN TO_TIMESTAMP('&start_time', 'YYYY-MM-DD HH24:MI')13 AND TO_TIMESTAMP('&end_time', 'YYYY-MM-DD HH24:MI')14 GROUP BY sql_id, NVL(event, 'ON CPU')15 ORDER BY COUNT(*) DESC16) WHERE ROWNUM <= 20;Save it as ora-ash-window.sql and run it with SQL> @ora-ash-window.
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…
- 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.
- 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.