Brief Summary
This video is a comprehensive guide to understanding and utilizing Automatic Workload Repository (AWR) and Active Session History (ASH) reports in Oracle databases. The video focuses on key metrics within AWR reports, including average active sessions (AAS), load profile, top weight events, and SQL statistics. It also explains the importance of ASH reports for capturing transient performance issues and identifying the specific SQL statements responsible for weight events. The video concludes with a demonstration of how to create a user account with the necessary privileges to generate AWR reports and a discussion on manual snapshot creation for targeted performance analysis.
- AWR reports provide valuable insights into database performance, including workload information, weight events, and SQL statistics.
- ASH reports are essential for capturing transient performance issues and identifying the specific SQL statements causing weight events.
- Manual snapshots can be created to capture specific performance events for targeted analysis.
Introduction to AWR Reports
The video begins by introducing the concept of AWR reports, which are statistical reports that capture information about the Oracle database's workload. AWR stands for Automatic Workload Repository, and it is generated by the M1 background process, which continuously collects system statistics, including user I/O, system operations, weight events, and other metrics. These statistics are stored in the AWR repository, which is the physical location of all AWR content.
Generating AWR Reports
AWR reports can be generated using SQL Developer. To enable this functionality, the DBA tab needs to be enabled in SQL Developer. Once a connection to the database is established, the AWR report can be generated. Generating AWR reports requires specific privileges, including "SELECT ANY DICTIONARY" and "DBMS_WORKLOAD_REPOSITORY". These privileges allow the user to query database statistics and generate AWR reports.
Analyzing AWR Reports: Key Metrics
The video then delves into analyzing AWR reports, focusing on key metrics that provide insights into database performance. The most important metric is the "DB Time", which represents the total time spent by the database processing operations. This metric is crucial for understanding the overall workload and identifying potential bottlenecks. Another important metric is the "Average Active Sessions" (AAS), which represents the average number of active sessions in the database during the AWR report's time period. A high AAS value indicates a busy system, and it can be used to determine if the CPU is being overutilized.
Load Profile: Redo Size, Logical Reads, and Physical I/O
The video then explores the "Load Profile" section of the AWR report, which provides information about the database's workload. This section includes metrics like "Redo Size", which indicates the amount of redo generated during the AWR report's time period. Redo is generated when data is modified in the database, and a high redo size can indicate a high volume of data changes. The "Logical Reads" metric represents the number of blocks read from the database buffer cache, while "Physical Reads" represent the number of blocks read from disk. A high number of logical reads indicates that the database is frequently accessing data from the buffer cache, which is generally efficient. However, excessive logical reads can lead to contention for latches, which can impact performance. Physical reads indicate that the database is accessing data directly from disk, which is less efficient than reading from the buffer cache.
Top Weight Events: Identifying Performance Bottlenecks
The "Top Weight Events" section of the AWR report is crucial for identifying performance bottlenecks. Weight events occur when a session has to wait for a resource to become available. The video highlights two common and problematic weight events: "Enqueue TX Row Lock Contention" and "Enqueue TM Contention". These events indicate that sessions are waiting for locks on rows or tables, respectively. A high number of these weight events can significantly impact database performance.
Understanding Weight Events and Timed Out Sessions
The video further explains the concept of weight events and how they are categorized into different weight classes. The "Application" weight class includes events related to application coding, such as "Enqueue TX Row Lock Contention" and "Enqueue TM Contention". The "User I/O" weight class includes events related to disk I/O, such as "DB File Sequential Read" and "DB File Scattered Read". The video also discusses the "Timed Out" metric, which indicates the number of sessions that were unable to acquire a lock within a specific time limit.
SQL Statistics: Analyzing SQL Performance
The "SQL Statistics" section of the AWR report provides detailed information about the SQL statements executed during the AWR report's time period. The video emphasizes the importance of analyzing the "Elapsed Time per Execution" metric, which indicates the time taken by a single execution of a SQL statement. SQL statements with high elapsed times are considered expensive and should be investigated for potential performance improvements. The video also discusses the "CPU Time per Execution" metric, which indicates the amount of CPU time consumed by a single execution of a SQL statement. SQL statements with high CPU times are considered CPU-intensive and may benefit from optimization.
Active Session History (ASH) Reports: Capturing Transient Issues
The video then introduces Active Session History (ASH) reports, which are used to capture transient performance issues that may not be evident in AWR reports. ASH reports provide a snapshot of the database's activity at a specific point in time, allowing for detailed analysis of session behavior and weight events. The video highlights the advantage of ASH reports over AWR reports in capturing short-lived performance spikes.
Analyzing ASH Reports: Identifying Problematic SQL Statements
The video demonstrates how to analyze ASH reports to identify the specific SQL statements responsible for weight events. By examining the "Active Session History" section of the ASH report, users can identify the SQL statements that were waiting on specific weight events, such as "Enqueue TX Row Lock Contention". This information is crucial for understanding the root cause of performance issues and implementing appropriate solutions.
Automatic Diagnostic Management (ADM) Reports: Recommendations for Improvement
The video concludes by discussing Automatic Diagnostic Management (ADM) reports, which provide automatic recommendations for improving database performance. ADM reports analyze AWR and ASH data and suggest potential solutions, such as increasing the size of the SGA or adding more CPUs. However, the video emphasizes the importance of carefully evaluating ADM recommendations and not blindly implementing them.
Creating a User Account with AWR Privileges
The video concludes with a demonstration of how to create a user account with the necessary privileges to generate AWR reports. The demonstration involves creating a new user account, granting the "SELECT ANY DICTIONARY" and "DBMS_WORKLOAD_REPOSITORY" privileges, and then connecting to the database using SQL Developer. Once connected, the user can access the AWR report viewer and generate AWR reports.
Manual Snapshot Creation for Targeted Analysis
The video concludes with a discussion on manual snapshot creation for targeted performance analysis. This technique allows users to capture specific performance events, such as a load test, by manually creating snapshots before and after the event. This provides a more focused analysis of the database's behavior during the event, allowing for more accurate identification of performance issues.
