Getting Started Querying AWR Data

Submitted by dave on Wed, 04/27/2011 - 16:41

Intro to the Data Model

ToDo

All History Related Views

SELECT * 
  FROM dba_views 
 WHERE view_name LIKE 'DBA_HIST%';

What are the most useful views?
Snapshot Related
DBA_HIST_SNAPSHOT

SQL Related
DBA_HIST_SQLSTAT
DBA_HIST_SQL_PLAN

Instance Related
DBA_HIST_SGASTAT
DBA_HIST_IOSTAT_FUNCTION

DBA_HIST_SNAPSHOT - The Glue
Most Relations revolve around DBA_HIST_SNAPSHOT

FieldDescription
SNAP_IDUnique snapshot ID
DBIDDatabase ID for the Snapshot
INSTANCE_NUMBERUsually 1 - Can be >1 for RAC systems
STARTUP_TIMEStartup time when Snapshot was taken.
BEGIN_INTERVAL_TIMEBeginning period for snapshot
END_INTERVAL_TIMEActual time snapshot was taken

STARTUP_TIME important!
Counters do not persist across instance restarts
AWR Report generation procedures will throw error
Any querying should take this into account
If you see snaps older than your purge retention window hanging around don't be alarmed - they are probably associated with a Baseline. Baseline snaps are not purged after retention window. See DBA_HIST_BASELINE for more info on configured Baselines.

Snapshots From Previous 7 Days

SELECT * 
  FROM DBA_HIST_SNAPSHOT 
 WHERE BEGIN_INTERVAL_TIME >= (SYSDATE-7)
 ORDER BY BEGIN_INTERVAL_TIME;