Last updated on April 15th, 2017 at 12:31 pm
Automatic Workload Repository (AWR) is a collection of persistent system performance statistics owned by the SYS user. It resides in SYSAUX tablespace. By default snapshot are generated once every 60min and maintained for 7 days.AWR report are used to investigate performance and other issues.
Script Availabe in $ORACLE_HOME/rdbms/admin
awrrpt.sql:Depending on the reasons for collecting the report, the default can be used, or for a more focused view, a short 10-15 minute snapshot could be used.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.
Here are some of the important facts and features about AWR repository
AWR collects data in the following categories:
• Base Statistics – general database performance metrics since instance start-up.
• SQL – statistics for each executed SQL statement (# executions, # physical reads, etc)
• Deltas – the rate of change of important stats over time. Similar to our collection technologies that do
before and after snapshots and only show the deltas over the specified period of time.
• Expert Advice – results of the expert analysis engine provided in 10g.
AWR is automatically installed and running with 10g. The new MMON process is responsible for collecting data andpopulating the AWR.The MMON process takes snapshots of performance data at regular intervals and inserts that data into the AWR tables. The tables containing AWR information are stored in the SYSAUX tablespace (also new in 10G) under the SYS schema. The AWR related tables all begin with “WR”
The third letter of each table name signifies the type of data that it contains
I – advisory functions (SQL Advice, Space Advice, etc)
• M – metadata information
• H – historical data
Oracle also adds views on top of these base tables. The views all begin with DBA_HIST.Some of the views are given below
Workload Repository Views
V$ACTIVE_SESSION_HISTORY – Displays the active session history (ASH) sampled every second. 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.
Useful Operations on AWR
a) How to Modify the awr snapshot settings.
retention => 21600, — Minutes (21600 = 15 Days).
— Current value retained if NULL.
interval => 60); — Minutes. Current value retained if NULL.
b) Dropping the AWR snaps in range:
c) Creating a SNAPSHOT Manually: