General approach for troubleshooting using ASH

ASH (Active Session History) is a core diagnostic tool in OceanBase Database that records the history of active sessions. It helps DBAs quickly identify performance bottlenecks and root causes of failures.

Based on the complexity of troubleshooting, the issues that can be resolved using ASH are categorized into two types:

• Type 1 issues: These can be identified by specific metrics in ASH. Examples include abnormal CPU resource usage, abnormal I/O resource usage, bottlenecks in specific wait events, and resource consumption at the session level.

• Type 2 issues: These require integrating information from multiple dimensions in ASH using a divergent methodology. These issues demand a higher level of expertise from ASH users.

Based on the above categorization, ASH offers significant advantages over traditional monitoring in the following scenarios:

1. Real-time dynamic troubleshooting: When SQL response times suddenly increase or system resources encounter bottlenecks, traditional monitoring cannot observe all active sessions in the database. ASH can capture fine-grained information in real time.

2. Cross-node troubleshooting in distributed systems: OceanBase Database's SQL execution chain typically involves multiple OBServer nodes. ASH can associate execution information across nodes using identifiers such as trace_id and sql_id, enabling end-to-end tracing.

3. Execution phase-level troubleshooting: ASH can observe the execution of SQL operators at the operator level. Combined with wait events and execution phases, it provides information that plan_monitor cannot offer.

4. Resource contention and competition analysis: When multiple sessions compete for the same resource (such as locks or cache) or a single session consumes excessive CPU resources, ASH can precisely troubleshoot by tracking real-time active sessions and associating wait events.

5. Session-level troubleshooting: For scenarios such as long-term abnormal behavior, blocking, or long transactions in a single session, you can directly filter by session_id to analyze its historical behavior, providing more comprehensive diagnostic information than processlist.

6. Overhead analysis of background tasks: In the ASH model, background tasks are treated as background sessions. They are marked with attributes such as Program, Module, and Action, and their execution overhead can be analyzed in detail using dimensions such as wait events.

7. Cold start and initialization issues: Scenarios such as frequent session establishment and disconnection, slow SQL parsing, and slow plan generation are typically blind spots for traditional monitoring.

General troubleshooting approach

Step 1: Identify the performance issue

You can perform the following operations:

• Observe the system for a drop in QPS, an increase in RT, an abnormal CPU load, or a surge in IO waiting.

• Cross-verify the system with external tools such as OCP monitoring and Linux system tools (tsar/iostat).

Step 2: Obtain an ASH report

You can obtain an ASH report based on the initial analysis of the current scenario.

When you obtain an ASH report for the first time, we recommend that you do not specify any parameters except the time range. This way, you can generate a full cluster report to understand the overall situation. Then, based on the initial analysis results, you can generate more detailed reports for specific tenants, nodes, or SQL_IDs. HTML reports provide better readability and interactivity, so we recommend that you use them first.

• Generate a basic report (for the entire cluster).

  At least two parameters are required: the start time and end time of the report.

  For example, in the sys tenant, execute the following command to obtain the ASH report for the entire cluster.

  obclient(root@sys)[oceanbase]> CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT('2025-09-23 17:14:00', '2025-09-30 18:14:00',report_type=>'html') \G
  

• Obtain an ASH report for specific SQL_IDs.

  Assume that the specific SQL_ID is 1CA21C86CCF10D8635BF62C17BEA7128. In the sys tenant, execute the following command to obtain the ASH report.

  obclient(root@sys)[oceanbase]> CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT('2025-09-23 17:14:00', '2025-09-30 18:14:00', 
                                          sql_id=>'1CA21C86CCF10D8635BF62C17BEA7128',report_type=>'html') \G
  

• Obtain an ASH report for a specific OBServer node.

  Assume that the IP address of the OBServer node is 192.168.0.1. In the sys tenant, execute the following command to obtain the ASH report.

  obclient(root@sys)[oceanbase]> CALL DBMS_WORKLOAD_REPOSITORY.ASH_REPORT('2025-09-23 17:14:00', '2025-09-30 18:14:00', 
                                          svr_ip=>'192.168.0.1', svr_port=>'2828',report_type=>'html') \G
  

Step 3: Interpret the report and locate the issue

1. Initial issue location.

   All resource consumption (CPU, IO, locks, and so on) will be reflected as waiting events. ASH records these events by sampling. Focus on the following report modules:

   • Top Foreground DB Time: locates the main time-consuming events of foreground sessions.

   • Top Blocking Sessions: quickly identifies the blocking source.

   • Top IO Events: distinguishes the IO source (log writing, user SQL, and background tasks).

   Typical scenario identification:

   • Row lock blocking: the row lock wait event occupies a large proportion.

   • CPU bottleneck: you need to combine the background tasks, parallel execution, retry events, and so on for comprehensive judgment.

   • IO bottleneck: the palf write event, DAG-related waiting events, or disk reads associated with SQL appear.

2. Perform detailed issue location based on the initial issue location.

   • Blocking source location. This applies to lock waiting scenarios.

     When the row lock wait event occupies a large proportion in the current ASH, perform the following operations to further locate the issue.

     1. Obtain the top blocking sessions.

        Here is an example:

        obclient(root@sys)[oceanbase]> SELECT blocking_session_id, count(1) AS cnt FROM oceanbase.GV$OB_ACTIVE_SESSION_HISTORY
            WHERE sample_time BETWEEN '2025-09-23 17:14:00' AND '2025-09-30 18:14:00' 
            AND event = 'row lock wait' 
            GROUP BY blocking_session_id 
            ORDER BY cnt desc;
        

        Based on the query result:

        • If a blocking_session_id occupies an absolute majority, a long transaction or slow query is holding the lock.

        • If multiple blocking_session_id values occupy similar proportions, a concurrency hotspot exists. This may be due to frequent updates of a single row, concentrated access to a hotspot partition, or uneven distribution of data between large and small accounts.

     2. Obtain the transaction ID that holds the lock.

        Based on the blocking session ID (blocking_session_id) obtained in the previous step, obtain the transaction ID that holds the lock. Here is an example:

        obclient(root@sys)[oceanbase]> SELECT tx_id, count(1) as cnt FROM oceanbase.GV$OB_ACTIVE_SESSION_HISTORY
            WHERE sample_time BETWEEN '2025-09-23 17:14:00' AND '2025-09-30 18:14:00' 
            AND blocking_session_id = xxxx 
            GROUP BY tx_id 
            ORDER BY cnt desc;
        

     3. Obtain the SQL_ID that holds the lock.

        Based on the transaction ID (tx_id) obtained in the previous step, obtain the SQL_ID that holds the lock. Here is an example:

        obclient(root@sys)[oceanbase]> SELECT * FROM oceanbase.GV$OB_SQL_AUDIT
            WHERE tx_id = xxx 
            ORDER BY request_time desc\G
        

        By performing the above progressive queries, you can locate the specific blocking session, transaction, and SQL statement.

   • Resource hotspot location. This applies to CPU/IO imbalance scenarios.

     When the CPU and IO issues are obvious, you can perform the following operations to troubleshoot the issues.

     • Route correctness: check the partition_hit column in the GV$OB_SQL_AUDIT view to confirm whether the ODP routing is balanced.

     • Data distribution: analyze the access frequency of tablet_id in ASH to identify the hot partitions.

     • Leader distribution: the leader nodes of a tenant will bear more internal RPCs and inner SQLs. Therefore, you need to check the backend load in ASH.

     • Parallel execution: when a large number of PX tasks appear in the ASH report, you need to adjust the parallelism based on the SQL.

     • Background tasks: waiting events with Program as DAG-related usually indicate IO or CPU consumption caused by major compactions.

     For more information about the troubleshooting operations, see Troubleshoot CPU usage imbalance and Troubleshoot IO bottlenecks.

Solutions

• For row lock blocking, you can try the following solutions:

  • Optimize the business logic that holds the lock to reduce the transaction duration.

  • Split hot rows or implement application-level throttling.

  • Continuously monitor the Top Blocking Sessions in the ASH report.

• For CPU usage imbalance, you can try the following solutions:

  • Correct the ODP routing rules to ensure traffic balance.

  • Adjust the partitioning strategy to disperse hot tablets.

  • Optimize the PX parallelism parameter.

  • Transform the SQLs with high retry events.

• For IO bottlenecks, you can try the following solutions:

  • Optimize the execution plans of SQLs with high disk reads.

  • Adjust the major compaction trigger timing.

  • Expand the IO resources or use a higher-performance storage medium.

References

• Generate an ASH report

• Analyze an ASH report

• Troubleshoot CPU usage imbalance

• Troubleshoot IO bottlenecks

• Wait events