Last updated on March 29th, 2019 at 05:40 pm
What is AWR?
AWR repository is a important part implemented in Oracle database 10g and above. It replaces the statspack utility. Since it is collected from SGA directly,performance impact is quite low.
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 60 min and maintained for 7 days. AWR report are used to investigate performance and other issues.
Script Available in $ORACLE_HOME/rdbms/admin
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
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.
Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
Compares detailed performance attributes and configuration settings between two selected time periods.
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 and populating 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”
WRM$_WR_CONTROL WRI$_AGGREGATION_ENABLED WRI$_ADV_USAGE WRI$_ADV_TASKS WRI$_ADV_SQLW_TABVOL WRI$_ADV_SQLW_TABLES WRI$_ADV_SQLW_SUM WRI$_ADV_SQLW_STMTS WRI$_ADV_SQLW_COLVOL WRI$_ADV_SQLT_STATISTICS WRI$_ADV_SQLT_RTN_PLAN WRI$_ADV_SQLT_PLANS WRI$_ADV_SQLT_BINDS WRH$_LATCH_CHILDREN_BL WRH$_LATCH_BL WRH$_JAVA_POOL_ADVICE WRH$_INSTANCE_RECOVERY WRH$_FILESTATXS_BL WRH$_FILEMETRIC_HISTORY WRH$_EVENT_NAME WRH$_ENQUEUE_STAT WRH$_DLM_MISC_BL WRH$_DB_CACHE_ADVICE_BL
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.
BEGIN DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention => 21600, -- Minutes (21600 = 15 Days). -- Current value retained if NULL. interval => 60); -- Minutes. Current value retained if NULL. END; /
b) Dropping the AWR snaps in range:
BEGIN DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range( (low_snap_id=>40, High_snap_id=>80); END; /
c) Creating a SNAPSHOT Manually:
BEGIN DBMS_WORKLOAD_REPOSITORY.create_snapshot(); END; /