Automatic Workload Repository report or AWR report collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This gathered data is stored both in memory and in the database, and is displayed in both reports and views.
Over the years Oracle has provided performance gathering tools and reporting tools. Earlier UTLBSTAT/UTLESTAT scripts were used for monitoring performance. With the introduction of Oracle 8i we got Statspack functionality. Automatic Workload Repository (AWR) was introduced with Oracle 10g and is still being used in 18c.
(Note: If you are just starting out or new to DataBase Performance tuning then I would suggest you check our previous post on Introduction to Performance Tuning In Oracle Database)
The AWR is a source for information and other Oracle features it Includes
- Wait events for identify performance problems.
- Time model statistics.
- Active Session History (ASH).
- Statistics from the V$SYSSTAT and V$SESSTAT views.
- Object usage statistics.
- SQL statements that are resource intensive.
- Automatic Database Diagnostic Monitor
- SQL Tuning Advisor
- Undo Advisor
- Segment Advisor
By default snapshot are generated once every 60 min and kept for 7 days
If you wish to generate awr snapshot manually, then it can be done by using the script below. This comes in handy, when there is a requirement to generate an awr report for an non-standard window which has smaller interval.
So we will generate two snapshot at different intervals for 5 mins each. AWR can be generated using begin_snap_id and end_snap_id.
Generating new snapshot
Checking the snapshots(whether new one is created or not)
A baseline is a snapshots that represents a specific period of usage. Baselines can be used to compare current performance against periods in the past with similar work loads. You can create baseline to represent a particular period of batch processing
The pair of snapshots associated are retained until the baseline is explicitly deleted.
Reading AWR Reports
The first thing you should do is run the ADDM report for the specific period of time. ADDM report provides analysis of the parts of the system that are consuming the most time. It is often recommended to start with the ADDM report to narrow down your focus area.
(Note: If you want to know more about ADDM then click here)
When you are looking at an AWR report, top 5 events near the top should be the place where you begin. Looking at Timed Foreground Events indicates the bottlenecks for a particular period of time in the system.
Please stay tuned for our coming post on performance Tuning we will be covering how to do ASH analytics.
These are just a glimpse of some of the factor which needs to be taken care of while considering the performance of your database.
You will get to know all of this and deep-dive into each concept related to Performance Tuning once you will get enrolled in our Performance Tuning On-Premise & Cloud
Another question, which might come to your mind, What are all the things you will get when you enrolled!!
Now it’s your turn to post your doubts in the comment section and let us know where you are facing challenges in Performance Tuning
- Overview of Automatic Database Diagnostic Monitor (ADDM)
- Oracle Database Performance Tuning Guide
- Introduction to Performance Tuning In Oracle Database
- Database Performance Tuning On-Premise & Cloud: Step by Step Hands-On Lab Exercise
- Database Performance Tuning: On-Premise & Cloud Training
- Performance Tuning Process
Click on the Image below and Get your FREE Guide to Start your journey with Performance Tuning. Learn from Beginner to Performance Tuning Guide and step forward with no doubts.