OracleASH & AWR
AWR settings and recent snapshots
Snapshot interval and retention, then every snapshot from the last 24 hours. You need the snapshot IDs to run an AWR report.
Not yet verified. How scripts are tested
1CLEAR COLUMNS2SET LINESIZE 120 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN snap_interval HEADING "INTERVAL" FORMAT A204COLUMN retention FORMAT A205COLUMN snap_id HEADING "SNAP ID" FORMAT 99999996COLUMN inst FORMAT 9997COLUMN begins FORMAT A168COLUMN ends FORMAT A59 10SELECT snap_interval, retention11FROM dba_hist_wr_control12WHERE dbid = (SELECT dbid FROM v$database);13 14SELECT snap_id, instance_number AS inst,15 TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24:MI') AS begins,16 TO_CHAR(end_interval_time, 'HH24:MI') AS ends17FROM dba_hist_snapshot18WHERE begin_interval_time > SYSDATE - 119ORDER BY snap_id, instance_number;Save it as ora-awr-snaps.sql and run it with SQL> @ora-awr-snaps.
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.
- 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…
- 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.