Automatic Workload Repository (AWR)-Vital Tool for Oracle DBA

1
944


Automatic Workload Repository (AWR)

Oracle provides wealth of advanced features including reporting and performance gathering tools like Automatic Workload Repository (AWR) which was introduced in the Oracle 10g pack. AWR presents complete collection of performance statistics relating to database problem detection and tuning. This is one of the important tool for Oracle database administrators.

The UTLBSTAT and UTLESTAT were earlier used to collect statistics over a period of time, but it was replaced with STATSPACK utility in Oracle8i. The STATSPACK utility was evolved into AWR with advanced functionalities to collect performance statistics. The AWR utility generates the snapshots of performance data such as segment-usage statistics, system and session statistics, high-load statistics and time-model statistics. It collects key data on the following:

  • Wait statistics – The wait events used to recognize database performance problems
  • Base statistics that includes the general performance metrics
  • SQL statistics – For each executed SQL statement
  • Time model statistics that indicates the database time associated with a specific process from the time model views(V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
  • Active session reports
  • ability to create, list and remove baselines

When Oracle database is created, the Automatic Workload Repository (AWR)  is installed and enabled automatically.  It automates the collection of statistics and is collected and stored in the Oracle database. The parameter STATISTICS_LEVEL is kept to TYPICAL (the default) or ALL to collect the Oracle database statistics. But If you are setting the STATISTICS_LEVEL  to BASIC, it will disable the AWR. The AWR  snapshot of 8 days can be retained in Oracle 11g as opposed to seven daysin Oracle 10g.

awr-utility-oracle-database

 AWR is primarily used for detection and analysis of problems as well as for self-tuning of database. It collects different types of statistics information to analyze the problems and the statistics are based on wait events, active session history statistics,  level statistics of different sessions, usage statistics of objects and SQL statements.

It consists of a number of tables and the names of these tables start with a special identifier “WR”.  The AWR tables are owned and managed by the SYS schema and stored in the SYSAUX tablespace.The identifier is followed by a  mnemonic which specifically identifies the designation type of the table and it is followed by a dollar sign ($). There are 3 types of designations for the AWR tables:

  • (WRM$) Metadata type
  • (WRH$) Historical data
  • (WRI$) Advisor function related

AWR snapshots

In Oracle database 10g, the snapshots are taken every hour and you can be retained 7 days. It is according to the default settings and you can alter it with the below procedure:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

    retention => 43200,        — Minutes (= 30 Days). Current value retained if NULL.

    interval  => 30);          — Minutes. Current value retained if NULL.

END;

/

For more information on AWR, check the link below:

 

AWR Enhancements in Oracle Database 11g Release 1

Hope this article on AWR and Oracle utilities helps. Feel free to share your queries in the comments section.

1 COMMENT

LEAVE A REPLY