Home

Active Session History (ASH) is a vital component in Oracle Database, providing real-time session data to assist in performance analysis. However, under certain conditions, ASH may perform emergency flushes, indicating potential undersizing issues. This guide explains how to diagnose and manage ASH emergency flushes effectively to maintain database performance.

What Is an ASH Emergency Flush?

An ASH emergency flush occurs when the Active Session History buffer becomes full, requiring immediate flushing to the Automatic Workload Repository (AWR). This situation often arises when the ASH size is insufficient for the database workload. Frequent emergency flushes can impact database performance and require corrective action.

Symptoms of ASH Emergency Flushes

The following message may appear in your database logs when an ASH emergency flush occurs:

Active Session History (ASH) performed an emergency flush. 
This may mean that ASH is undersized. If emergency flushes are a recurring issue, 
consider increasing ASH size by setting the value of _ASH_SIZE to a larger value.

You can monitor the total number of emergency flushes since the instance startup and the current ASH size using the following query:

SELECT total_size/1024/1024 AS ash_size_mb, awr_flush_emergency_count 
FROM v$ash_info;

Example Output:

ASH_SIZE_MB      AWR_FLUSH_EMERGENCY_COUNT
------------------ -------------------------
              128                          1

How to Increase ASH Size

If emergency flushes are frequent, increasing the ASH size can help mitigate the issue. The parameter _ASH_SIZE controls the ASH buffer size. Note that this is not a dynamic parameter, so changes require a database restart to take effect.

Steps to Increase ASH Size:

1. Set the desired ASH size in the SPFILE:

   ALTER SYSTEM SET "_ash_size" = 200M SCOPE=SPFILE;

2. Restart the database for the changes to take effect:

   SQL> SHUTDOWN IMMEDIATE;
   SQL> STARTUP;

3. Verify the new ASH size:

   SELECT total_size/1024/1024 AS ash_size_mb, awr_flush_emergency_count 
   FROM v$ash_info;

Important Notes:

• Ensure that the new ASH size aligns with your database workload and available memory resources.
• Avoid setting excessively large values, as it can impact memory allocation for other components.

Monitoring ASH Performance

Regularly monitor the following metrics to assess ASH performance and detect potential issues:

• ASH Size: Use the v$ash_info view to check the current size.
• Emergency Flush Count: Track the frequency of emergency flushes since the instance startup.

If you observe a consistent increase in emergency flushes, consider further tuning the _ASH_SIZE parameter or investigating workload patterns.

Conclusion

Managing Oracle ASH effectively is critical for ensuring optimal database performance. Emergency flushes signal that the ASH buffer size may be undersized for the current workload. By increasing the ASH size and monitoring key metrics, you can reduce the frequency of emergency flushes and maintain database stability.

Use the steps outlined above to diagnose, manage, and optimize your ASH configuration, ensuring smooth performance and efficient resource utilization.