How_to_read_and_use_Oracle_AWR_report.pptx

Know How on Oracle AWR

1. AWR (Automatically workload repository) Report 1

2. 2 What’s AWR? Automatically workload repository - built-in repository that exists in every Oracle Database. makes a snapshot of all of its vital statistics and workload information and stores them in the AWR at regular intervals.

3. Points to remember: 1. Collect Multiple AWR Reports:  It's always good to have two AWR Reports, one for good time (when database was performing well), second when performance is poor. We have two reports – running update with & without indices on the columns. 2. Stick to Particular Time:   Data base performing slow on heavy updates, heavy select, etc. It’s a good time to run AWR report on these specified time intervals. 3. Split Large AWR Report into Smaller Reports:   Instead of having one report for long time like one report for 1hrs. it's is better to have four reports each for 15 minutes.  3

4. How to analyze AWR: 1. Database Details: 4 2. Host Configuration: This gives the information about  platform CPU, socket  and  RAM.

5. How to analyze AWR ( Continued ): 5 3. Snap Shot Detail: This are the detail about snap shot taken,  Snap start time  and  end time . Difference between them is as " Elapsed ". Here is a new term " DB Time " With Indices Without Indices

6. How to analyze AWR ( Continued ): 6 With Indices Without Indices 4. Load Profile:

7. How to analyze AWR ( Continued ): 7 With Indices Without Indices 5. Instance Efficiency Percentages: It is always good to have value near 100% means most of the CPU resources are used into operations other than parsing, which is good for database health.

8. How to analyze AWR ( Continued ): 8 With Indices Without Indices 6. Top 5 Timed Foreground Events: This has a list of top 5 foreground wait events. Any event with ‘Concurrency’ then there could be some serious problem. Next,  "log file switch (checkpoint incomplete) " which has high waits , huge  Time (s)  and large values in  Avg Wait ( ms )  and  wait class  is configuration, this can be resolved by Adding REDO log files to groups or enlarge the existing REDO log files.

9. How to analyze AWR ( Continued ): 9 With Indices Without Indices 7. Time Model Statistics: This is a detailed explanations of system resource consumptions. Stats are order by Time (s) and % of DB Time.

10. How to analyze AWR ( Continued ): 10 With Indices 8. SQL Ordered by Elapsed Time: In this report, look for query has low  executions  and high  Elapsed time per Exec (s)  and this query could be a candidate for troubleshooting or optimizations. Also , if executions is 0, it doesn't means query is not executing, this might be the case when query was still executing and you took AWR report. That's why query completion was not covered in Report.  Captured SQL account for 82.3% of Total DB Time (s): 828 Captured PL/SQL account for 2.0% of Total DB Time (s): 828

11. 11 Without Indices Captured PL/SQL account for 0.0% of Total DB Time (s): 6,603 Captured SQL account for 19.7% of Total DB Time (s): 6,603

12. How to analyze AWR ( Continued ): 12 With Indices 9. SQL Ordered by CPU Time: In this report, SQL queries are listed on the basis of CPU taken by the query i.e. queries causing high load on the system. The top few queries could be the candidate query for optimization. Captured SQL account for 84.0% of Total CPU Time (s): 82 Captured PL/SQL account for 0.8% of Total CPU Time (s): 82

13. 13 Without Indices Captured SQL account for 43.1% of Total CPU Time (s): 136 Captured PL/SQL account for 0.0% of Total CPU Time (s): 136

14. Thank you! 14