Analyze and diagnose suspicious SQL statements, top SQL statements, and slow SQL statements - ApsaraDB for OceanBase

The SQL diagnostics feature is designed to analyze and diagnose TopSQL, SlowSQL, and Suspicious SQL to help you optimize database performance.

Basic concepts

The basic concepts of SQL diagnostics are as follows:

TopSQL: refers to SQL statements with the overall longest response time within the selected time range.
SlowSQL: refers to SQL statements whose execution time exceeds 500 ms.
Suspicious SQL: refers to SQL statements that may cause performance issues and are filtered out based on rules and algorithms.

Note

Currently, the system has the following restrictions on Standard Edition (Key-Value) cluster instances:

For KV cluster instances with version numbers greater than or equal to 4.2.4 but less than 4.3.0, the diagnostic information of TopSQL and SlowSQL can be displayed.
For KV cluster instances of other versions, only the diagnostic information of SlowSQL can be displayed.

TopSQL

TopSQL refers to SQL statements with the overall longest response time within the selected time range. You can perform the following operations related to TopSQL:

Filter TopSQL by quick filters, such as Full Table Scan, Multi-partition scan, and Multiple Remote Executions.
Click View Samples in the Operation column to view the SQL statement.
Click the plus sign (+) in front of the SQL text to view the last error and error summary during the statistical period.
Click View Optimization Suggestions in the upper-right corner to go to the optimization center and view optimization suggestions.
Click TopSQL Comparison in the upper-right corner to compare the status and performance of the same SQL statement in different periods on the same node or in the same period on different nodes.
- Comparison of different periods: Specify the baseline period and comparison period. Click Comparative Analysis to view the Comparison of Details and Comparison of Performance Trends.
- Comparison of different nodes: Specify the baseline node and comparison node. Click Comparative Analysis to view the Comparison of Details and Comparison of Response Time Trends.
  Note
  - Two SQL statements with the same SQL ID in the same database are considered the same.
  - Comparison of different nodes is performed only when the same SQL statement is executed on different nodes. Otherwise, no comparison result will be displayed.
When the CPU utilization of a host is too high, you can filter the top SQL statements by database and node, and then sort these SQL statements by the CPU time in descending order to locate the SQL statements that need to be optimized the most.

SlowSQL

SlowSQL refers to SQL statements whose execution time exceeds 500 ms.

Filter SlowSQL by quick filters, such as Full Table Scan, Multi-partition scan, and Multiple Remote Executions.
Click View Samples in the Operation column to view the SQL statement.

Suspicious SQL

Suspicious SQL refers to SQL statements that may cause performance issues and are filtered out based on rules and algorithms. The following table lists common diagnostic results and troubleshooting suggestions.

Diagnostic result	Troubleshooting suggestion
Hint with no effect	It indicates an abnormal SQL statement that requires the database administrator (DBA) to determine whether to fix the execution plan by using an outline.
Full table scan with poor performance	Analyze the execution plan and table structure to ensure that SQL has appropriate indexes available.
Poor performance despite the use of indexes	Check whether the performance is acceptable in your business scenarios. If not, improve the business logic or optimize the indexes.
Performance degradation	Check data distribution changes, request surges, and execution plan changes.
Plan change accompanied by performance degradation	Check with the DBA. You can use an outline to fix the execution plan to verify whether the SQL statement is abnormal.
Execution spikes	Check the business volume. A spike in the number of executions of an SQL statement may cause the overall performance to decrease.
Overhigh CPU load	Check your business scenarios, data distribution changes, request increases, and execution plan changes.
Suspected lock conflict	SQL is being retried, which may be caused by a lock conflict. Please investigate the business logic to determine if a lock conflict scenario exists.
Too many partitions are accessed by the DML statement	Rewrite the SQL statement so that it supports partition pruning.
Suspected buffer table	Check whether the related table is being frequently updated.
There may be large and small account numbers	Check whether there is a data skew issue.
Execution plan instability	Check whether there are issues with plan regression.
Plan generation time is too long	Check whether there are issues such as low plan hit rate.

The Suspicious SQL tab displays SQL Text, SQL ID, Database, Diagnostic Results, and so on.