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: SQL statements with the overall longest response time within the selected time range.
SlowSQL: SQL statements whose execution time exceeds 500 ms.
Suspicious SQL: SQL statements that may cause performance issues. Such statements are identified and determined by the platform based on rules and algorithms.

TopSQL statements

TopSQL statements are SQL statements with the longest response time and are sorted in descending order.

Filter TopSQL statements by quick filters, such as Full Table Scan, Multi-partition scan, and Multiple Remote Executions.
Click the plus sign (+) in front of a TopSQL statement to view the last error and a summary of errors about the SQL statement 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 or on different nodes.
- Comparison of different periods: Select a period for comparison with the baseline period. You can define the period for comparison and the baseline period. Click Comparative Analysis to view the comparison of details and the comparison of performance trends.
- Comparison on different nodes: You can compare the execution of an SQL statement on the baseline node with that of the same SQL statement on other nodes. You can select multiple nodes for comparison. The system obtains the average SQL execution result on these nodes for comparison with the baseline node. After you select or define a period for analysis, 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 can be considered the same.
  - Comparison on different nodes is performed only when the same SQL statement is executed on the nodes. If the baseline node and the nodes for comparison did not execute the same SQL statement, no comparison result is displayed.

SlowSQL statements

SlowSQL statements are SQL statements whose execution time exceeds 500 ms.

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

Suspicious SQL statements

Suspicious SQL statements are SQL statements that may cause performance issues. Such statements are identified and determined by the platform based on rules and algorithms. The following table describes the general suspicious SQL 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.