What is AWR( Automatic workload repository ) and How to generate the AWR report?

About:

Automatic workload repository is a collection of persistent system performance statistics owned by SYS.
It resides in SYSAUX tablespace. By default snapshot are generated once every 60min and maintained for 7 days by default.

you can generate AWR reports by running SQL scripts:

1)The awrrpt.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids.

2)The awrrpti.sql SQL script generates an HTML or text report that displays
statistics for a range of snapshot Ids on a specified database and instance.

3) The awrsqrpt.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids. Run this report
to inspect or debug the performance of a SQL statement.

4) The awrsqrpi.sql SQL script generates an HTML or text report that displays
statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.

5) The awrddrpt.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods.

6) The awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

Script Location: $ORACLE_HOME/rdbms/admin

To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql
script at the SQL prompt:


Few Views which helps while generating the AWR report

DBA_HIST_SNAPSHOT
DBA_HIST_WR_CONTROL
DBA_HIST_BASELINE

How to Modify the AWR SNAP SHOT SETTINGS:
=====================================

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 30); -- Minutes. Current value retained if NULL.
END;
/


Creating the Baseline:
======================

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 10,
end_snap_id => 100,
baseline_name => 'AWR First baseline');
END;
/


Dropping the AWR baseline:
==========================

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range(
(row_snap_id=>40,
High_snap_id=>80);
END;
/


Creating SNAPSHOT Manually:
===========================

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_snapshot();
END;
/


Workload Repository Views:
==========================

The following workload repository views are available:

* V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
* V$METRIC - Displays metric information.
* V$METRICNAME - Displays the metrics associated with each metric group.
* V$METRIC_HISTORY - Displays historical metrics.
* V$METRICGROUP - Displays all metrics groups.
* DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
* DBA_HIST_BASELINE - Displays baseline information.
* DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
* DBA_HIST_SNAPSHOT - Displays snapshot information.
* DBA_HIST_SQL_PLAN - Displays SQL execution plans.
* DBA_HIST_WR_CONTROL - Displays AWR settings.