All about AWR ( Automatic Workload Repository)



Last updated on April 15th, 2017 at 12:31 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 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”

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;


Leave a Reply