ASH Report Evaluation

ASH Report Overview

An ASH report provides a granular view of session activity over a specified time period. It includes details about wait events, SQL executions, session IDs, blocking sessions, and more. Here's how to generate an ASH report:

Generating ASH Report

bash

Copy code

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

• You'll be prompted to specify the start and end times for the report, or a range based on the latest data.

• The report will be generated in HTML or text format.

2. Key Sections of an ASH Report

A. Load Profile

This section gives a summary of the workload, showing the average number of active sessions, wait events, and resource consumption.

• Average Active Sessions: Indicates the average number of sessions that were active during the time window.

• DB Time: Represents the total time spent by all sessions in active work during the snapshot.

• %CPU / %IO Wait: Indicates how much time is spent on CPU vs. I/O waits. High CPU utilization suggests CPU-bound issues, while high I/O wait times point to I/O bottlenecks.

B. Top Wait Events

This section lists the most significant wait events by time, along with the number of sessions affected.

• Event: Shows the wait event name (e.g., db file sequential read, log file sync, etc.).

• Wait Class: Categorizes the type of wait (e.g., User I/O, System I/O, Concurrency, Network, etc.).

• Avg Active Sessions: Indicates how many sessions, on average, were waiting on this event.

• Time (s): Total time spent waiting on this event.

Focus on events with high percentages or long wait times. For example:

• User I/O: Issues with reading from or writing to disks.

• Concurrency: Locking or contention problems.

C. Top SQL by DB Time

This section provides details about the SQL queries that consumed the most database time during the snapshot period.

• SQL ID: The unique identifier for the SQL statement.

• SQL Text: The actual SQL query.

• DB Time: Time spent executing the SQL.

• Executions: Number of times the SQL was executed.

• CPU Time: The amount of CPU resources consumed by the query.

• Wait Time: Time spent waiting for resources (I/O, locks, etc.).

Queries with high DB Time and high CPU Time or Wait Time are prime candidates for optimization. Check if you can tune SQL statements with high elapsed time or excessive resource consumption.

D. Top Sessions

This section identifies which sessions contributed the most to the database workload during the snapshot period.

• Session ID (SID): Unique identifier for the session.

• DB User: The database user running the session.

• SQL ID: The SQL statement being executed.

• Wait Event: Current wait event for the session.

• % Activity: Percentage of activity attributed to this session.

This section helps identify problem sessions that may be consuming excessive resources. A session with high CPU Time and frequent waits could indicate a bottleneck or a poorly optimized query.

E. Top Blocking Sessions

This section identifies sessions that are causing lock contention by blocking other sessions.

• Blocking Session ID: The session that is causing the block.

• Blocked Sessions: The number of sessions being blocked.

• Wait Event: What the blocked sessions are waiting for.

Blocking sessions are often caused by poorly designed transactions or long-running queries. You may need to investigate and either tune the SQL or manage the locks better.

F. Top Objects

Shows the database objects (tables, indexes) that were involved in the most waits during the report period.

• Object Name: The name of the table or index.

• Owner: The schema that owns the object.

• Waits: The number of waits that occurred while accessing the object.

If an object appears frequently, it may indicate a hotspot in the database. Index tuning or partitioning strategies can sometimes help reduce contention.

3. Analysis Steps for ASH Report

Step 1: Review the Average Active Sessions

Start by checking the Average Active Sessions (AAS) in the load profile section to understand if the database is overloaded. AAS greater than the number of CPUs in your system could indicate CPU pressure or I/O bottlenecks.

Step 2: Identify High Wait Events

Next, check the Top Wait Events section to see where the database is spending the most time waiting. Focus on events with the highest wait times and drill down to the sessions and SQL statements causing these waits.

Step 3: Examine Top SQL Statements

Go through the Top SQL by DB Time section to identify poorly performing SQL. Look for queries with high execution times or high I/O costs. These SQL statements are potential targets for tuning (e.g., adding indexes, rewriting queries).

Step 4: Investigate Sessions Causing Resource Contention

Use the Top Sessions and Top Blocking Sessions sections to identify any sessions that are hogging CPU or causing contention. You may need to tune these sessions or adjust application behavior.

Step 5: Examine Hot Objects

Review the Top Objects section to find objects (tables or indexes) that are frequently accessed or causing contention. Consider database design improvements such as indexing, partitioning, or caching strategies.

4. Recommendations Based on ASH Report

After evaluating the ASH report, some possible recommendations might include:

• SQL Tuning: Optimize queries with high execution times, high buffer gets, or high CPU usage.

• I/O Optimization: Address high User I/O or System I/O waits by tuning queries, modifying table designs, or improving disk performance.

• CPU Management: If AAS indicates CPU pressure, consider adding more CPU resources or optimizing CPU-bound queries.

• Lock and Concurrency Management: Investigate long-running transactions or blocking sessions to minimize lock contention.

• Memory Optimization: Adjust memory settings (e.g., SGA, PGA) if memory pressure is observed.

Conclusion

ASH reports provide a detailed view of session activity and wait events, allowing DBAs to pinpoint performance issues. By focusing on key sections such as wait events, SQL statements, sessions, and objects, you can diagnose bottlenecks and implement targeted optimizations to improve Oracle database performance.

ASH (Active Session History) and AWR (Automatic Workload Repository) are both Oracle performance diagnostic tools, but they serve different purposes and provide different types of information. Here’s a comparison of ASH and AWR:

1. Purpose and Scope

ASH (Active Session History)

• Purpose: Provides real-time, detailed snapshots of active sessions in the database. It focuses on current session activity and wait events, offering insight into what is happening right now.

• Scope: Captures data every second about active sessions, including wait events, SQL execution, and session activity. It is ideal for diagnosing performance issues that are occurring in the short term or during specific events.

AWR (Automatic Workload Repository)

• Purpose: Provides historical performance data collected over time, offering a broader view of database performance trends and issues. It is useful for analyzing performance over longer periods.

• Scope: Collects and stores performance data periodically (every hour by default), including metrics on wait events, SQL execution, system performance, and more. It’s designed for trend analysis and diagnosing issues over extended periods.

2. Data Collection and Frequency

ASH

• Collection Frequency: Captures detailed snapshots every second.

• Data Retention: Retains a small amount of recent data (usually about an hour) in memory. The retention period can be adjusted through the ASH settings.

• Granularity: Provides very granular, real-time data on active sessions.

AWR

• Collection Frequency: Captures snapshots at regular intervals (usually every hour).

• Data Retention: Stores performance data for longer periods, typically days or weeks, depending on the retention settings.

• Granularity: Provides aggregated data over specified periods, offering a high-level view of performance trends.

3. Data Provided

ASH

• Data Focus: Real-time session activity, wait events, SQL executions, session IDs, and blocking sessions.

• Key Sections: Load Profile, Top Wait Events, Top SQL by DB Time, Top Sessions, Blocking Sessions, and Top Objects.

• Usage: Diagnosing immediate performance issues, identifying problematic sessions or queries in real-time.

AWR

• Data Focus: Historical performance data, including wait events, system statistics, SQL performance, and I/O statistics.

• Key Sections: Load Profile, Top 5 Timed Foreground Events, Instance Efficiency Percentages, SQL Statistics, and System Statistics.

• Usage: Analyzing performance over time, identifying long-term trends and recurring issues, and understanding overall system health.

4. Reporting and Analysis

ASH

• Reporting Tools: ASH reports can be generated using the ashrpt.sql script. These reports provide detailed information about active sessions and their activities during the selected time period.

• Analysis: Best for short-term diagnostic tasks, such as identifying the source of a performance spike or investigating issues occurring in the recent past.

AWR

• Reporting Tools: AWR reports can be generated using the awrrpt.sql script. These reports provide a comprehensive overview of database performance over the snapshot period.

• Analysis: Best for long-term performance analysis, understanding trends, and identifying recurring issues. AWR reports help in capacity planning and historical performance analysis.

5. Use Cases

ASH

• Use Case Examples:

  • Diagnosing performance issues during peak load times.

  • Identifying and resolving session-level performance bottlenecks.

  • Investigating specific events or queries causing high wait times.

AWR

• Use Case Examples:

  • Analyzing overall database performance over days or weeks.

  • Identifying trends and recurring issues over time.

  • Conducting capacity planning and performance tuning based on historical data.

6. Summary

• ASH: Provides detailed, real-time snapshots of active session activity and wait events, ideal for diagnosing immediate performance issues.

• AWR: Offers a historical view of performance data, useful for understanding long-term trends, analyzing system health over time, and identifying recurring performance problems.

Both ASH and AWR are valuable tools for database performance analysis, with ASH focusing on real-time session data and AWR providing a broader, historical perspective.