FAQ: Automatic Workload Repository (AWR) Reports
(Doc ID 1599440.1)
Last updated on JUNE 13, 2023
Applies to:
Oracle Database Exadata Express Cloud Service - Version N/A and laterOracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.
Purpose
This document addresses a number of Frequently Asked Questions related to Automated Workload Repository (AWR) Report and some related reports (such as ADDM (Automatic Database Diagnostic Monitor) and ASH (Automatic Session History) Reports).
These Questions and Answers were generated from the Q&A Session for "Troubleshooting DB Performance issues with AWR" Webcast. The recording for this webcast can be accessed in the following document:
<Document 1597373.1> Archive of Database Performance Related Webcasts
You can discuss this article as well as addressing any further questions in the Database Performance Community in this thread.
A video entitled: "Introduction to Performance Analysis Using AWR and ASH" is also available here providing a comprehensive guided tour using an actual problem.
Questions and Answers
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Purpose |
| Questions and Answers |
| Gathering an AWR Report on CDB and PDB |
| How to setup an instance to gather AWR reports |
| Interpretation |
| Is there a video explaining how to interpret AWR information? |
| What is the principal event that causes performance issues? |
| What does the AWR DB time represent? |
| Why does the DB time exceed elapsed time at the top of the report? |
| Why does the sum of the percentages of DB time under "Time Model Statistics" equal more than 100? |
| How do you account for multiple CPUs in AWR reports |
| In the Load Profile, how do you interpret DB Time(s) of 63.8 (64) seconds VS DB CPU(s) of 1.8 seconds ? |
| How to obtain CPU consumption by user for each hour from AWR? |
| What is the difference between the instance CPU and host CPU sections of the AWR report? |
| What do waits for cpu+wait for cpu indicate ? |
| How do support interpret AWR? Does Oracle support have tools to interpret the results from an AWR? |
| I see high number of waits for 'Event XYZ' in my AWR report. How should I proceed? |
| In the Top 5 Timed Foreground Events 'event XYZ' has high "time(s)" and "avg wait(ms)". What does that mean? |
| Where do we find the "event XYZ" in the AWR? It is not listed in the top waits. |
| What is meant "User calls" and "Parses" ? |
| What is SQL ordered by Parse Calls? |
| How do I interpret the "SQL ordered by Physical Reads (UnOptimized)" Section? |
| Why does the report not concentrate on cursor waits? |
| If the number of executions is very small (for example one execution), does this mean that the sql needs to be tuned? |
| How do I interpret the "Buffer Cache Advisory" Section? |
| Is the "Av Rd (ms)" from disk or from a buffer ? |
| Does Invalidations or Reloads in the SQL Area indicate the Cache is insufficient? |
| What are the meanings of the "Library Cache Activity" Namespaces |
| How should I deal with high waits for 'Buffer Busy Waits' on RAC? |
| How do I interpret the "PGA Memory Advisory" Section? |
| How do I interpret the "OS Statistics" Section? |
| Can you use AWR reports to identify DB performance issue due to physical limitation of hardware or some problem with configuration /SQL ? |
| In AWR Efficiency Percentages, "Parse CPU to Parse Elapsd %" we are consistently getting very low % (i.e. 0.3%) but every other Efficiency Percentages is >95% |
| Does AWR report provide any PQ stats/usage? |
| How accurate are the advisory sections in the AWR ? |
| What is the reference time/values to consider in the AWR for checking any problem? |
| If we have a deadlock, can we find out in an ADDM, AWR or ASH report? |
| Why is the number of SQL executions sometimes equal to zero? |
| Why are "Executions" and "Elap per Exec(s)" are 'Blank' in AWR for Some SQL Statements? |
| How to find .... using AWR |
| Is it possible to report the activity/access for a certain table using AWR/ASH/ADDM? |
| How can you obtain the CPU consumption by a user for each hour from AWR? |
| How do I find a long-running DDL statement within the AWR report? |
| Is there a way to find all the memory used the by the database including all the user processes within a certain time frame? |
| ADDM |
| How is ADDM/ASH related to AWR? |
| In Oracle 12c or higher, not all of the ADDM reports are included in my AWR report . Why is this? |
| I have found the addmrpt.sql goes directly to the problem. But it seems like awrrpt.sql gives more detail, but I have found addmrpt.sql more useful. Any comments on this? |
| In terms of ADDM and SGA sizing the ADDM always seems to suggest SGA is undersized, how reliable it this as a counter? Is there a better way to determine the optimal SGA size ? |
| How can confirm if my database has enough memory allocated to it ? |
| Can I reduce cache waits by adding RAM to the database? |
| What is the best way to determine that the hardware is not enough and more needs to be added? |
| Would adding new HW make all these events go away, for example adding cpu or memory ? |
| What does swapping mean ? |
| Are you more likely to encounter swapping is you do not have sufficent RAM, especially in the case of multiple instances on the same machine? |
| Should I get worried if I see that the system is swapping, does that indicate a need to add more physical RAM ? |
| ASH |
| Why (in 12c or higher) don't I see the ASH report included in my AWR report? |
| What is the performance impact of running ASH four times in an hour? |
| Can an ASH report generate the same level of detail as a 10046 trace? |
| At what time interval is ASH captured from memory? |
| How can you reduce the 10 second time for ASH samples stored in dba_hist_active_sess_history ? |
| Gathering interval |
| What is the minimum interval possible by AWR snapshot? |
| The interval for AWR reports is normally generated using the snapshots created automatically (eg. every 30 mins). Is there a way to manually create snapshots so I can generate AWR with any interval? |
| If we had 4 AWR reports of 15 minutes each, then does that mean we do not need ASH reports? |
| Can we use a sql tuning task on AWR history information? |
| Can the AWR run timeframe be modified to run for periods in the past? |
| Is there any way to generate multiple hourly AWR reports at once instead of generating them one at a time interactively? |
| Transferring AWR Data to a Remote AWR Repository |
| Is there a way we can automatically send old snapshot of all prodcution DB's to a remote DB before they get purged based on retention? |
| If it is not automatic, is there a way to transfer snapshots to Remote DB's which can store snap of all production DB's manually? How can we implement it? |
| Maintenance |
| If a database has been created as a copy of another database it may contains 'cloned' AWR information. Is it possible to clear the old cloned instance AWR/ASH report history from database. |
| Is there a best way to cleanup the AWR data after DBID changes? |
| Licensing |
| What products/feature packs need to be licensed to use AWR/ASH/ADDM ? |
| Can AWR/ASH/ADDM be used on Oracle Standard Edition? |
| Do we need to have a separate license to use AWR/ASH/ADDM from OEM ? |
| Miscellaneous |
| Do we have any healthcheck reports to check overall DB performance issue besides running ADDM/AWR reports for bad period time? |
| Are there any special considerations for the Automatic Workload Repository (AWR) when migrating databases to 11g? |
| AWR does not give recommendations on things found in the report. How do I get recommendations? |
| Is it possible to baseline AWR reports and is this worthwhile? |
| Why does generating an AWR report in HTML format takes much more time than AWR in TXT format? |
| Where can we get more information on specific events and system calls ? |
| What is the difference between latch and lock ? |
| Is oradebug still being used? |
| Is there a "performance" parameter in Oracle? |
| What is is difference between TKProf and AWR reports? |
| Discuss Diagnosis of Performance Issues Using AWR Reports |
| References |