Oracle 19c Diagnosing Performance Issues using ASH Reports

In Oracle, diagnosing performance issues using Active Session History (ASH) reports is essential for maintaining database efficiency. ASH Reports provide valuable insights into performance bottlenecks and help optimize the database. This article will explore ASH Performance Diagnosis, Database Performance Tuning, and the steps to effectively use ASH Reports for performance monitoring.

Understanding ASH Report Performance Diagnosis

ASH Performance Diagnosis involves analyzing database performance metrics to identify issues affecting efficiency. ASH collects detailed data from active sessions, including SQL_ID, object number, file number, block number, wait event identifier, SID, module, action name, client identifier, service hash identifier, and blocking session. This information is crucial for maintaining optimal database performance and ensuring smooth operations.

ASH continuously samples session activity in the system and stores the history of database time. This proactive approach helps database administrators (DBAs) to anticipate potential issues and take corrective actions before they impact the system. ASH is always on for first fault analysis and does not require replaying the workload. Additionally, ASH memory consumption does not exceed a maximum bound of 5 percent of the Shared Pool/SGA.

Using ASH Reports for Performance Diagnosis

ASH data is stored in two main views:

1. Current Active Session History: This is available from the buffer and can be queried using V$ACTIVE_SESSION_HISTORY.
2. Historical Active Session History: This is available from disk and can be queried using DBA_HIST_ACTIVE_SESS_HISTORY and WRH$_ACTIVE_SESSION_HISTORY (partitioned).

These views provide a comprehensive view of both current and historical session activity, enabling DBAs to diagnose performance issues effectively.

Generating an ASH Report

To generate an Active Session History report, follow these steps:

1. Access the Performance Page in OEM: Navigate to the Performance page and select the desired timeframe for analysis.
2. Generate the ASH Report: Use the following script to manually generate the Active Session History report:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

The ASH report will highlight session activity and identify performance bottlenecks, providing insights into areas that may require optimization.

📢 You might also like: Oracle 19c Using Automated Maintenance Tasks to Manage Performance (Category: Performance Management and Tuning)

ASH Reports – ASH Tuning Session

An ASH tuning session follows the same steps as a manual tuning session, but with automation to streamline the process:

1. Generate Active Session History report: ASH runs automatically and samples session activity continuously.
2. Review the recommendations: Examine the findings displayed on the Database Home page in OEM.
3. Implement the recommendations: Apply the suggested changes to optimize performance.
4. Review the next ASH report: Ensure the implemented changes have resolved the issues.

Manual Tuning Session

A manual tuning session involves more steps compared to an ASH tuning session:

1. Collect current statistics: Gather the latest performance data.
2. Compare current statistics with a previous set: Identify deviations.
3. Look up in a performance-issues knowledge base: Research known issues and solutions.
4. Define the problem and make recommendations: Propose a solution.
5. Build a trial solution: Develop a test fix.
6. Implement and measure the change: Apply and evaluate the fix.
7. Decide if the solution met the goal: Assess the effectiveness of the fix.

Benefits of ASH Reports

ASH Reports offer several benefits for database performance management:

1. Proactive Monitoring: ASH helps identify performance issues before they impact the system, allowing for timely intervention.
2. Trend Analysis: By comparing current performance with historical baselines, DBAs can identify trends and plan for future capacity needs.
3. Performance Optimization: Recommendations from Active Session History reports guide DBAs in optimizing database performance.
4. Efficient Resource Allocation: Understanding performance trends helps in better allocation of resources, ensuring optimal usage.

Conclusion

In conclusion, ASH Reports are a powerful tool for diagnosing performance issues in Oracle databases. By using the insights provided by ASH reports, DBAs can ensure their databases run efficiently and effectively. Implementing Active Session History Reports not only helps in maintaining optimal performance but also provides valuable insights into performance trends and potential issues.

Start using ASH Reports in your Oracle environment today to enhance your performance monitoring and ensure your database operates at peak efficiency.

See more on Oracle’s website!

Be Oracle Performance Management and Tuning Certified Professional, this world is full of opportunities for qualified DBAs!