Yuan Yao, An Oracle ACE's Blog

This blog is an example of how to use an ASH report to detect a transient performance issue.

ASH report begins with basic information about the environment in which the report was generated:

The main sections of the ASH report include:

• Top Events
• Load Profile
• Top SQL
• Top PL/SQL
• Top Java
• Top Sessions
• Top Objects/Files/Latches
• Activity Over Time

Except for the last section, all sections focus on identifying the top entities from different dimensions. By reviewing these sections, we can detect anomalies based on our experience and knowledge.

Top Event

The “Top Events” section reveals that the enq: TM - contention event accounts for a significant portion of the total activity (82.26%). This event typically indicates contention on a table-level lock, often related to DML operations such as inserts, updates, or deletes.

Top SQL

In the “Top SQL” section, five listed SQL IDs are associated with the same event, enq: TM - contention, and are all UPDATE statements on the STOCK_ITEM table.

Top Sessions

The “Top Sessions” section shows multiple sessions are experiencing high enq: TM - contention, consistent with the earlier observations from the “Top Event” and “Top SQLs” sections.

Top Objects/Files/Latches

The “Top Objects/Files/Latches” section indicates that the enq: TM - contention event is primarily affecting the YUAN.STOCK table.

Activity Over Time

The “Activity Over Time” section is divided into 10 time slots, each containing session activity information for that particular time slot. When examining this section, we typically look for spikes in the Event Count and Slot Count columns. These spikes often indicate performance problems due to higher workload and/or resource contention. In this example, two consecutive time slots (14:56:00 and 14:58:00) show significant spikes in enq: TM - contention, aligning with previous findings from other sections of the report.

Conclusion

Based on prior observations, we can safely conclude that severe contention on  YUAN.STOCK table , manifested by the enq: TM - contention event , occurred from 14:56:00 to 15:00:00. This transient performance issue was detected by ASH report, which might be diluted in otherwise diagnostic tools.