Automatic Workload Repository (AWR) is a vital tool for database administrators to detect issues that are in the database or are about to develop. This built-in repository (residing in SYSAUX tablespace) makes snapshots of the most critical statistics at regular intervals and stores them as AWR.
Every database admin or anyone who wants to tune the database has to learn how to read and understand the AWR report. It is not an easy step to do for you have to understand the architecture, what are the parameters set for the database, how the DB is configured, what is the load of the DB, and many more. In spite of this, I will try to introduce you to AWR, how to read it, on what to focus and will highlight some important parts that should be easy to read and take action upon.
Only privileged users can generate AWR report. See the necessary roles and permissions:
- EXECUTE on DBMS_WORKLOAD_REPOSITORY
- SELECT on DBA_HIST_DATABASE_INSTANCE and DBA_HIST_SNAPSHOT
There are two basic ways to generate the AWR report:
- Enterprise Manager
- Log in
- From the overview select a database you want to generate AWR for
- Performance -> AWR -> AWR Report
- Select SNAPSHOT range (begin, end)
- Click on the “Generate Report” button
- AWRRPT.sql
- execute the script @$ORACLE_HOME/rdbms/admin/awrrpt.sql
- specify the report type (generally HTML)
- specify the SNAPSHOT range
- specify report name
A few important notes before you generate the report!!
- Split AWR report into smaller pieces
- by doing so, you can easily determine the windows when the issue is happening
- Pick the peak time for generating the report
- if you are doing general maintenance – pick the peak time of the DB
- if someone told you “Hey, the database was slow yesterday” .. ask him to specify the time and use this for generating the report
- Generate the report for different days as well (same time)
- one report when the DB was not performing well
- and one report when the DB was performing well
Now, that you know where to look and how to generate it. Let’s look at the source views where AWR looks too:
- V$ACTIVE_SESSION_HISTORY – Displays the active session history (ASH) sampled every second.
- V$METRIC – Displays metric information.
- V$METRICNAME – Displays the metrics associated with each metric group.
- V$METRIC_HISTORY – Displays historical metrics.
- V$METRICGROUP – Displays all metrics groups.
- 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.
These views are key sources for your “manual” investigation if you are “old-school” 😉
AWR is divided into multiple sections and I will now describe each section and suggest what to look for there.
AWR report sections’ details
Main Report
Database summary – details about your database; such as hostname, RAC (yes,no), and so on.
Database instance details – details about your instance; such as instance name, version, and so on
Report summary – high-level overview of the snapshot range
Cache sizes – the size of each SGA region
OS statistics by instance – number of CPUs, load %, and so on …
Global Activity Load Profile – this is a very important section. It shows you how big was the load in the database per transaction per second.
SQL Statistics
In this section, you can find key SQL queries causing issues.
- SQL Ordered by
- Elapsed Time – shows queries that took significant amount of time
- CPU Time – shows queries that consume significant amount CPU time
- User I/O Time (Global) – shows queries that consume a high amount of I/O
- Gets – shows queries that performed a high amount of logical reads
- Reads – shows queries that performed a high amount of physical disk reads
- Executions – shows queries with the most executions
System Statistics
Thorough details about what was happening in the database – how many index scans, direct reads, writes, parses, lob reads, commits, redo log usage, temp space allocation, and so on.
Segment Statistics
Segment statistics tell us what segments were used, how much, for what particular action, and so on.
I/O Statistics
This section gives important information about I/O activity.
Library Cache Activity
This section describes how shared library objects are used/managed.
Init.ora parameters
This is the original init.ora parameters set during the AWR report generation.