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(
- 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 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
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:
retention => 43200, — Minutes (= 30 Days). Current value retained if NULL.
interval => 30); — Minutes. Current value retained if NULL.
For more information on AWR, check the link below:
Hope this article on AWR and Oracle utilities helps. Feel free to share your queries in the comments section.