Oracle - SQL diagnostic reports

AWR (Automatic Workload Repository) report: Oracle through snapshots collects, process and maintains performance statistics that can be accessed via AWR reports.

Generating AWR report:
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Also see: these related AWR reports under the same location:
— awrrpt.sql
Displays various statistics for a range of snapshots Ids.
— awrrpti.sql
Displays statistics for a range of snapshot Ids on a specified database and instance.
— awrsqrpt.sql
Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.
— awrsqrpi.sql
Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
— awrddrpt.sql
Compares detailed performance attributes and configuration settings between two selected time periods.
— awrddrpi.sql
Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

ASH (Active Session History) report: displays top session activities during AWR snapshots.

Generating ASH report: SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
SQL> @$ORACLE_HOME/rdbms/admin/ashrpti.sql

ADDM (Automatic Database Diagnostic Monitor) report: shows most significant performance issues between AWR snapshots.

ADDM reports include :
Top SQL Activities
CPU bottlenecks
Undersized memory allocations
Excessive parsing
I/O usage
Concurrency issues
Object contention

Generating ADDM report:
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql