The Oracle’s Automatic Workload Repository [AWR] collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. The report generated by AWR is a big report and it can take years of experience to actually understand all aspects of this report. In this post we will try to explain some important sections of AWR, significance of those sections and also some important tips. Please note that explaining all sections of AWR will not be possible so we will stick to some of the most frequently used sections.
Note that this is not comprehensive information and goal is to help in giving an overview of few key sections to Junior DBAs as a primer and to encourage them to build further the knowledge in related fields.
To start with let us mention some high level important tips regarding AWR:
1. Collect Multiple AWR Reports: It’s beneficial to have two AWR Reports, one for the good time and other when performance is poor or you can create three reports [Before/Meantime/After reports] during the time frame problem was experienced and compare it with the time frame before and after.
2. Stick to Particular Time: You must have a specific time when Database was slow so that you can choose a shorter timeframe to get a more precise report.
3. Split Large AWR Report into Smaller Reports: Instead of having one report for long time like one report for 3 hrs. it is better to have three reports each for one hour. This will help to isolate the problem
4. FOR RAC, take each instance’s individual report: For RAC environment, you need to do it separately of all the instances in the RAC to see if all the instances are balanced the way they should be.
5. Use ASH also : Use AWR to identify the troublesome areas and then use ASH to confirm those areas.
6. Increase the retention period : Some instances where you get more performance issues you should increase the retention time so that you can have historical data to compare.
TIME UNITS USED IN VARIOUS SECTIONS OF AWR REPORTS
-> s – second -> cs – centisecond – 100th of a second -> ms – millisecond – 1000th of a second -> us – microsecond – 1000000th of a second
Top Header
Performance Tuning Basics 1 : Selectivity and Cardinality
I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies [and to write about them].
How to find average active sessions in Oracle?
Go to the Performance Hub. In the ASH Analytics tab, select a dimension in the Average Active Sessions by drop-down list.
What is average active sessions in AWR?
Average Active Sessions [AAS] is the ratio of the change in Database Time [time in the database in seconds spent using CPU, IO, waiting] divided by the clock time. It is not a measure of how many sessions exist in the system at a given time, but rather how busy the database is.
What is difference between AWR and ADDM?
The Automatic Database Diagnostic Monitor [ADDM] is a tool that analyzes data in the Automatic Workload Repository [AWR] performing accurate and timely diagnosis of the problem. It locates the root cause and provides recommendations for correcting the problem.
How do you analyze AWR report for performance issues?
Here are 6 steps:.
Figure out why you're analyzing the report and what info to get from it..
Note details like database ID, instance number and snapshot range in the header..
Analyze CPU, I/O, and memory usage stats to find performance problems..
Identify SQL queries that take too much time or cause bad performance..