Create and analyze Oracle AWR

Oracle AWR (Automatic Workload Repository) provides an insight of a running Oracle database by periodically, e.g. every 15 min, collecting different system metrics (CPU, memory, I/O, wait events, etc.).

Generate AWR report

connect oracle with OS authentication:

sqlplus / as sysdba

run the sql statments:

@?/rdbms/admin/awrrpt.sql

answer the questions like report type with html or plain text, range of snapshot IDs, report name, etc. After that the report with the given name is saved in the current directory where sqlplus is started.

Remarks

AWR in Oracle is activated by default with one hour snapshot interval and retention time is 8 days. So snapshots older than 8 days will be removed. All snapshots can be accessed by selecting the view DBA_HIST_SNAPSHOT.