Oracle AWR Report | Automatic Workload Repository [Link]

net/oracle/oracle-awr-report

[Link]
Search site...  

Oracle AWR Report |

Automatic Workload
Repository
Table of Contents

• Introduction
• What is the Automatic Workload Repository (AWR)?
• Key Features of Oracle AWR Report
• How to Generate Oracle AWR Reports
• Generate Oracle AWR Reports using SQL*Plus Script
• Conclusion

Introduction

The Automatic Workload Repository (AWR) is a

fundamental component of Oracle Database that assists in
performance tuning and monitoring. AWR collects and stores
performance data critical for diagnosing and resolving
performance bottlenecks in your database. In this article, we
will explore how to effectively generate and analyze Oracle
AWR reports to optimize your database performance.

What is the Automatic Workload

Repository (AWR)?

The Automatic Workload Repository (AWR), introduced in

Oracle Database 10g, is an integral part of every Oracle

1 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

Database. It automatically captures and stores performance

statistics, such as wait events, system activity, and object
usage, that are crucial for identifying and troubleshooting
database performance issues. By default, AWR snapshots are
taken every hour and are retained for eight days, though these
settings can be customized.

Key Features of Oracle AWR Report

• Automatic Data Collection: AWR collects

performance data automatically at specified intervals.
• Historical Data Retention: Stores historical data for
trend analysis and long-term performance
management.
• Comprehensive Metrics: Includes metrics on wait
events, system activity, SQL statistics, and more.
• Integration with Oracle Enterprise Manager (OEM):
AWR data can be viewed and analyzed within OEM
for enhanced usability.

How to Generate Oracle AWR

Reports

Generating an Oracle AWR report is a straightforward

process. Below are the steps to generate a report using
SQL*Plus:

Step 1: Connect to the Database

Open SQL*Plus and connect to your database as a user with

DBA privileges:

sqlplus / as sysdba

Step 2: Run the Oracle AWR Report

Script

2 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

Execute the [Link] script located in the

$ORACLE_HOME/rdbms/admin directory:

SQL> @?/rdbms/admin/[Link]

Step 3: Follow the Prompts

The script will prompt you for the report format (HTML or text),
the snapshot IDs for the start and end of the reporting period,
and the output file name. The output file will be generated in
the $ORACLE_HOME/rdbms/admin directory.

Generating AWR Reports using

SQL*Plus Script

The following SQL*Plus script allows you to generate both

HTML and text-based AWR reports. The script requires the
database ID (DBID), instance ID, begin snapshot
ID, and end snapshot ID as arguments.

Find the Snapshot IDs

Before generating the AWR report, you need to identify the

snapshot IDs for the period you are interested in. Run the
following query to list available snapshots:

SQL> select
snap_id,
dbid,
instance_number,
begin_interval_time,
end_interval_time
from
dba_hist_snapshot
order by
snap_id desc;

SNAP_ID DBID INSTANCE_NUMBER BEGIN_INTERVAL_TIME

3 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

---------- ---------- --------------- ---------------------------

3 3358061257 1 01-JUN-24
2 3358061257 1 01-JUN-24
1 3358061257 1 31-MAY-24

SQL>

Execute the Script

Save the following SQL as awr_report.sql and use

SQL*Plus to execute the script with the appropriate arguments
(DBID, instance ID, begin snapshot ID, and end snapshot ID).
For example:

--DBID: 3358061257
--instance ID:1
--begin snapshot ID:2
--end snapshot ID:3

sqlplus system@dbdocs @awr_report.sql 3358061257

Save the following SQL as "awr_report.sql"

4 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

set feedback off

set heading off
set linesize 10000
set pagesize 0
set termout off
set trimspool on
set verify off

define dbid=&1
define inst=&2
define bid=&3
define eid=&4

column awr_html_file new_value awr_html_file noprint

column awr_text_file new_value awr_text_file noprint

select 'awr_report_' || &&dbid || '_' || &&inst

select 'awr_report_' || &&dbid || '_' || &&inst

set termout on
prompt Generating html AWR report
set termout off

spool &awr_html_file

select
output
from
table(
dbms_workload_repository.awr_report_html
l_dbid => &&dbid,
l_inst_num => &&inst,
l_bid => &&bid,
l_eid => &&eid
)
);

spool off

set termout on
prompt Generating text AWR report

5 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

set termout off

spool &awr_text_file

select
output
from
table(
dbms_workload_repository.awr_report_text
l_dbid => &&dbid,
l_inst_num => &&inst,
l_bid => &&bid,
l_eid => &&eid
)
);

spool off

quit

HTML and Text Reports

The script generates two files: an HTML report and a text

report in the same location where the awr_report.sql file is
located. The file names are in the format
awr_report_dbid_inst_bid_eid.html and
awr_report_dbid_inst_bid_eid.txt .

Conclusion

The Automatic Workload Repository (AWR) is an invaluable

resource for monitoring and tuning Oracle Database
performance. Regularly generating and analyzing Oracle
AWR reports allows database administrators to proactively
manage performance and address issues before they
escalate. Whether using SQL*Plus or Oracle Enterprise
Manager, leveraging AWR is crucial for maintaining a high-
performing Oracle Database.

6 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

Related content

• How to identify and troubleshoot deadlocks in the Oracle?

• Oracle AWR Report | Automatic Workload Repository
• SQL AWR Reports
• Oracle Explain Plan | Understand & Analyze Oracle Execution
Plans

• Flushing a Bad SQL Plan from Shared Pool

• SQL Tuning in Oracle with DBMS_SQLTUNE for Optimal
Performance

• Optimize Oracle SQL with SQL Health Check (SQLHC)

• Checking Tablespace Usage in Oracle
• Oracle Database Statistics and Cost-Based Optimizer for
Performance

• How to Check Oracle Database Statistics

• Analyzing Top Wait Events in Oracle
• Identifying Top Current SQL Queries Consuming Time in
Oracle

• Identifying Historically Expensive SQL Queries in Oracle

• Retrieving SQL Version Information in Oracle
• Analyzing Wait Events for Specific SQL in Oracle
• Analyzing Wait Events for Specific SQL Between Time
Intervals in Oracle

• Identifying SQLs Causing Wait Events Between Snapshots in

Oracle

• How to Enable SQL Tracing for User Sessions in Oracle

• How to identify table names for LOB segments in Oracle?

 Rate Your Experience

Like : 89 Dislike :1

 Last updated in Feb, 2025

7 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

 Useful Tools
Database Growth Calculator
Plan DB growth and storage needs now!

RAID Storage Calculator

RAID, performance, and redundancy!

Database Backup Size Calculator

Instantly calculate database backup size!

Base64 Encoder & Decoder

Encode or decode Base64 text and files!

SQL Beautifier | SQL Formatter

Beautify SQL with syntax highlights!

Unix Timestamp Conversion

Convert Unix time to human-readable date

Cron Job Generator

Online Cron Job Generator for Unix scheduling

IP Subnet Calculator
IP Subnet Calculator for IPv4/IPv6 ranges

Network Latency Test Tool

Measure network latency - Speed & Stability

Bandwidth Calculator
Calculate website bandwidth and data speeds

Encode and Decode URL Online

Quickly encode or decode URLs online

Online Word Counter

Count words, characters, and keyword density

Unit Conversion Calculator

Convert units like length, weight, and temperature

Random Password Generator

Generate strong, unique passwords for security

Password Strength Checker

Check password strength and improve protection

 Read more | Learn more

 Cloud Technology
Software as a Service (SaaS)
Understanding SaaS

Platform as a Service (PaaS)

Understanding PaaS

Infrastructure as a Service (IaaS)

Understanding IaaS

8 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

Understanding Private Cloud

Private Cloud Insights

Understanding Hybrid Cloud

Hybrid Cloud Insights

Understanding Kubernetes | K8s

Kubernetes Overview

Kubernetes Commands for Beginners

Essential K8s Commands

Kubernetes Best Practices

Optimizing K8s Deployment

Managing Kubernetes
K8s Cluster Management

CI/CD Pipeline
Automating Development Workflows

AWS Security Groups

Understanding Stateful Security

Microservices | Stateful vs Stateless

Comparing Stateful and Stateless

Cloud Data Protection

Securing Cloud Data

 Read more | Learn more

 Oracle Database
How to install Oracle 21c on Linux?
Step-by-Step guide for Oracle 21c installation

How to Install Oracle 19c on Linux?

Step-by-Step guide for Oracle 19c installation

Automating Database Startup and Shutdown

Using systemd to manage Oracle Database

How to Configure DataGuard in Oracle

DataGuard configuration step-by-step

Oracle AWR Report

Understanding Oracle AWR reports

SQL AWR Reports

Understanding SQL AWR reports in Oracle

Oracle Explain Plan | Execution Plans

Understand Oracle execution plans

Identifying Top Current SQL Queries

SQL queries consuming time in Oracle

How to Identify and Troubleshoot Deadlocks

Resolving Oracle deadlock issues

Identifying Historically Expensive SQLs

9 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

Optimize expensive queries in Oracle

Identifying Top Current SQL Queries

Fix time-consuming SQL queries in Oracle

Dynamic CPU Scaling | Resource Manager

Managing CPU resources in Oracle

How to Configure Huge Pages in Oracle

Setting up huge pages in Oracle

Checking Tablespace Usage in Oracle

Monitor and manage tablespace usage

Oracle 19c Flashback Database

Learn Oracle 19c flashback feature

Oracle Data Pump - expdp and impdp

Learn Oracle Data Pump commands

How to Resolve ORA-01403: No Data Found

Fix the ORA-01403 error in Oracle

ORA-12537: TNS: Connection Closed

Fix TNS connection closed errors

ORA-20001: Maximum Web Service Requests

Fix the ORA-20001 error in Oracle

Resolving ORA-3135: Connection Lost

Fix the ORA-3135 connection error

 Read more | Learn more

 MSSQL Database
Optimizing SQL Server on VMware
Best practices for SQL Server

SQL Server TempDB Best Practices

Best practices for TempDB performance

Updating Database Statistics in SQL

Update SQL Server statistics

SQL Query to Find Unused Indexes

Find unused indexes in SQL Server

How to get SQL Server config details

Retrieve SQL Server config info

How to find Missing Indexes

Find missing indexes in SQL Server

How to find table and index stats

Find table & index stats in SQL Server

Sessions Blocking chain tree

View blocking chain sessions

Identifying Blocking Sessions

Locking & blocking sessions

10 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

Identifying Locked Rows in Tables

Locked rows in SQL Server

Identifying Current session Locks

Identify current session locks

Index Usage Statistics

Understand index usage stats

Monitoring Application Sessions

Monitor sessions with T-SQL

Exploring Database with T-SQL

Explore SQL Server database

SQL Server query plan cache

Query plan cache in SQL Server

Database I/O Latency

Analyze I/O latency

Managing Index Fragmentation

Handle index fragmentation

Managing Fragmented Tables

Handle fragmented tables

Understanding Lock Escalations

Lock escalation management

Identifying Top Wait Events

Top SQL wait events

 Read more | Learn more

 PostGres Database
PostgreSQL Anonymizer: Data Masking
Data masking in PostgreSQL

How to Install PostgreSQL on Linux?

Install PostgreSQL on RedHat Linux

Handful PostgreSQL Commands

Commonly used PostgreSQL commands

How to Restart the PostgreSQL Service

Restart PostgreSQL service on Linux

How to Install Extensions in PostgreSQL?

Install PostgreSQL extensions

Generating a UUID in PostgreSQL

Create a UUID in PostgreSQL

Postgres Host Authentication Methods

Understand Postgres auth methods

Understanding pg_catalog Schema

Understand pg_catalog schema in PostgreSQL

Troubleshooting Blocked Queries

11 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

Fix long-running or blocked queries

Analyze Postgres Performance with Logging

Logging activity & pgBadger for performance

Optimizing PostgreSQL: Shared Buffers

Tuning shared buffers for PostgreSQL

PostgreSQL Vacuuming Best Practices

Best practices for vacuuming in PostgreSQL

Analyzing and Vacuuming Tables in Postgres

Vacuum and analyze tables in PostgreSQL

Best Practices for Managing Postgres Stats

Manage PostgreSQL statistics effectively

Tracking SQL Statements with pg_stat

Track SQL statements in PostgreSQL

pg_hint_plan: Control Execution Plans

Control execution plans in PostgreSQL

Understanding PostgreSQL Cache Hit Ratio

Analyze PostgreSQL cache hit ratio

Resolving Password Authentication Failed

Fix password authentication issues

Resolving FATAL: Database does not exist

Fix database not found error

Understanding and Analyzing Index Usage

Analyze index usage in PostgreSQL

 Read more | Learn more

 Linux
How to Configure Swap Space in Linux?
How to configure swap space in Linux

How to Install Oracle VirtualBox on Windows

How to install Oracle VirtualBox on Windows

Installing RHEL Linux 9 on a Virtual Machine

How to install RHEL Linux 9 on VM

How to Change Hostname in Linux?

How to change the hostname in Linux

How to Configure an Offline YUM Repository?

How to set up offline YUM repo in RHEL 9

How to Set Up the X Display in Linux?

How to set up X display in Linux

Adding a New Disk to VM in Linux?

How to add new disk to Linux VM

How to Install Linux 8 on a VM?

How to install Linux 8 on VM

12 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

 Read more | Learn more

 ASP/C#
How to Encrypt a Connection String in .NET
Secure [Link] connection string

Resolving 'ConfigProtectionProvider' Error

Fix 'ConfigProtectionProvider is Not Allowed' error

How to Secure Session Variables in .NET

Secure session variables in [Link]

Logging Event Auditing Information in .NET

Log events and audits in [Link]

Implementing a Simple CAPTCHA in .NET

Add CAPTCHA to forms in [Link]

 Read more | Learn more

 Online Tests
Oracle Proficiency Test
Over 100+ Questions & Answers for Oracle

SQL Server Proficiency Test

Over 100+ Questions & Answers for SQL Server

PostGreSQL Proficiency Test

Over 90+ Questions & Answers for PostgreSQL

Linux Proficiency Test

Over 100+ Questions & Answers for Linux

Basic MSSQL Objective Questions

Basic MSSQL Assessments for Beginners

Advanced MSSQL Objective Questions

Level 2 MSSQL Objective Assessments

Expert MSSQL Objective Questions

Level 3 MSSQL Objective Assessments

Basic Postgres Objective Questions

Basic PostgreSQL Assessments for Beginners

Advanced Postgres Objective Questions

Level 2 PostgreSQL Objective Assessments

Expert Postgres Objective Questions

Level 3 PostgreSQL Objective Assessments

 Read more | Learn more

13 of 14 16/10/2025, 20:01
Oracle AWR Report | Automatic Workload Repository [Link]

Quick Links Resources Extras

 Home  Useful Tools  About Us

 Oracle Database  Online Test  Contact Us
 SQL Server Database  Linux  Privacy Policy
 PostgreSQL Database  ASP/C#  Disclaimer
 HANA Database  Web Stories  Cookie Policy
 Cloud Technology

Connect With Us

    

 Munich, Germany, DE 80807

 admin@[Link]

© 2025 [Link]. All Rights Reserved.

14 of 14 16/10/2025, 20:01