Analyzing the AWR Report
The Oracle Automatic Workload Repository (AWR) collects and maintains performance statistics for problem detection and self-tuning purposes.The report generated by AWR is typically large and requires significant years of experience to fully understand all aspects.
The process is made harder by the fact that short module codes are used and it is not clear which package and line of code needs attention. In this article we will explain the use of Enginatics AWR Blitz Reports that removes the complexity in analyzing AWR in conjunction with Oracle EBS programs.
Note that this is not comprehensive guide and is aimed at showing you the faster way to diagnose using Blitz Reports which can be scheduled through the standard concurrent manager and emailed on a periodic basis thereby providing a reference point at all times.
It’s sensible to take snapshots at fixed times so that you can gauge the typical workloads during the day, week or month end activities. Running the reports more frequently will make it easier to analyse in smaller chunks and also provide historical benchmark comparisons at each node.
Example standard report
Blitz Report Library
The report library can be found here. The screen shot below lists a few of the seeded reports provided free with Blitz Report. In this article, we focus on the DBA AWR SQL Performance Summary report.
To review the steps in this tutorial simply download the free version of Blitz Report here or watch the video link at the bottom of the article.
Running the Blitz Report
Once you have installed Blitz Report, go to system administrator and open the form or instead review on our demonstration system here.
Blitz Report opens and allows selection of the library AWR reports and other useful diagnostic administrator reports such as FND Concurrent Requests, etc.
Select and run the DBA AWR SQL Performance Summary report, select parameters that you need from the list as shown below.
You can use double click on the report to complete the selection
The parameters are as follows:
| Display Seq | Parameter Name |
|---|---|
| 10 | SQL Text contains |
| 40 | Module Type |
| 50 | Module contains |
| 60 | SQL Id |
| 62 | Plan Hash Value |
| 65 | Show Bind Values |
| 67 | Schema |
| 70 | Package Name |
| 75 | Date From |
| 78 | Date To |
| 80 | Request Id (Time Restriction) |
| 90 | Daytime or Night hours |
| 92 | Day of Week |
| 95 | Level |
Once all parameters are entered to narrow the data according to your needs, the Blitz Report opens allowing analysis.
An example of the report can be found below
DBA_AWR_SQL_Performance_Summary_25-Jan-2019_160632
Highlights
The report returns the functional names and responsibilities, together with the code and code line number that is badly performing. It can be filtered based on type, e.g. Form, Concurrent Request, Business Event, etc.
Example below:
Evaluation of Blitz Report
To evaluate any of the above reports, download and install the free version of Blitz Report.
We think the best way to find out what Blitz Report can do for your business is a live demonstration.
Depending on your location we can schedule an onsite appointment or a remote video call. Or in the meantime, you can review the short video overview below.
The Blitz Report™ software is free to use for your first 30 reports, you can plan a 30 minutes guided installation with the team at enginatics.com