AWR (Automated Workload Repository) in 10g Rel 2

Let me share some thing with you on AWR

AWR Consist

-   Active session history (ASH)

-   High load SQL statements

-   Time model statistics at the database level as well as at the session level for CPU Usage and wait classification.

-   Object statistics that record usage as well as access counts for segments such as tables, indexes and other database objects.

-   Traditional V$SESSTAT, V$SYSSTAT, V$SYSTEM_EVENT AND V$SESSION_EVENT data.

Where does AWR stay?

-   Oracle kernel allocates a small but distinct portion of the SGA to buffers that are dedicated to holding session history and other AWR related information.

-   This in-memory buffer is updated by MMNL and MMON background processes.

All the AWR Objects stat with one of the following prefixes

-   WRH$ tables stores workload repository history details. Some tables are Range Partitioned by DB_ID & SNAP_ID Column

-   WRI$ tables stores workload repository internal details

-   WRM$ tables stores workload repository metadata