SQL basic information - ApsaraDB for OceanBase - Alibaba Cloud Documentation Center

This topic introduces the basic information about the SQL Insights page, including SQL statistics, performance monitoring, tenant-level request analysis, performance monitoring, and SQL list.

Cluster statistics

On the Statistics tab, you can view the number of suspicious SQLs and the number of SlowSQLs in the current cluster, as well as the corresponding number for each tenant. Click a tenant name, and the system will display the tenant's diagnostics details in the lower sections of the SQL Insights page.

Cluster performance monitoring

On the Performance Monitoring tab, you can view the SQL performance of the cluster.

Tenant request analysis

Click the View Request Analysis button in the middle part of the page to view the Distribution of Elapsed Time and Request Statistics List.

Tenant performance monitoring

Click View Performance Monitoring in the middle part of the page to view the performance monitoring data of a tenant.

Tenant diagnostic information

You can select a tenant from the Tenant drop-down list and view the diagnostic information about the TopSQL, SlowSQL, Suspicious SQL, High-risk SQL, and New SQL for that tenant. You can also perform the following operations:

Filter the SQL diagnostic information by Database, Node, Duration, or Quick Filter.
- The Duration option allows you to view data for the last 5 minutes, last 30 minutes, last hour, and last 6 hours. You can also customize the duration, but note the following limitations:
  - Maximum Range Limit: The selected time range must not exceed 24 hours.
  - Minimum Range Limit:
    - For data within 3 days: The selected time range must be at least 1 minute.
    - For data from 3 to 7 days: The selected time range must be at least 5 minutes.
    - For data beyond 7 days: The selected time range must be at least 20 minutes.
- The Quick Filter option supports Full Table Scan, Multi-partition Scan, Multiple Remote Executions, Hard Parsing, Error Executions, and Retry Executions.
- If the default filters do not meet your needs, you can click Expand to filter by SQL ID, SQL text, or add more conditions.
Click the icon for a column to sort the list by the column.
Click Column Management to select the columns to be displayed in the list.
Click Export in the upper-right corner to download the list.
Click an SQL statement to go to its details page. On the Overview page, you can view the SQL text, physical execution plans, SQL execution history, table information, and advanced settings of the SQL statement.
Enable Aggregate in. This feature aggregates the results of IN queries without affecting statistics. You can move your pointer over the SQL ID of an SQL statement identified by Aggregated to view the SQL IDs of other aggregated SQL statements.
For SQL statements that contain IN clauses whose content is different numbers of simple constants, if the Aggregate in feature is not enabled, the SQL diagnostics feature considers them as different types of SQL statements. If the Aggregate in feature is enabled, the SQL diagnostics feature considers them as the same type of SQL statements and aggregates statistics of these SQL statements into the same row.

Top SQL statements

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

Filter top SQL statements by quick filters, such as Full Table Scan, Multi-partition Scan, and Multiple Remote Executions to quickly locate SQL statements that need to be optimized.
Click the plus sign (+) in front of a top SQL statement to view the last error and all errors that occurred.
Click View Optimization Recommendation 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 Comparison of Performance Trends.
- Comparison of 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 of 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.
You can diagnose the top SQL statements of a specified tenant or all tenants in a cluster in real time.
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.

Slow SQL statements

SQL statements whose execution time exceeds 500 ms are defined as slow SQL statements.

You can perform the following operations related to slow SQL statements:
- Filter slow SQL 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 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.
Full table scan with poor performance
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.

High-risk SQL statements

High-risk SQL statements are SQL statements that may cause damage to system performance, security, and data.

The following table describes the types of high-risk SQL statements that can be identified.

Operation	Examples	Risk type	Description
Add or delete a column	`alter table test drop column id` `alter table test add new_id int not null comment 'New ID'`	Schema change - Columns are added or deleted	-
Delete a table or database	`drop table test` `drop database test`	A table or database is deleted	-
Empty a table	`truncate table test`	A data table is cleared	-
Update without conditions	`update test set new_id =1`	Update without conditions	-
Update with a permanently true condition	`update test set new_id =1 where 1 = 1`	Update with a permanently true condition	-
Delete without conditions	`delete from test`	Delete without conditions	-
Delete with a permanently true condition	`delete from test where 1=1`	Delete with a permanently true condition	-
Too many rows returned	`max_return_rows >= 50000`	Too many rows returned	The `max_return_rows` parameter specifies the maximum number of returned rows allowed for an SQL statement. If the number of returned rows exceeds this value, the SQL statement is considered at high risk.
Too many rows affected	`max_affected_rows >= 50000`	Too many rows affected	The `max_affected_rows` parameter specifies the maximum number of affected rows allowed for an SQL statement. If the number of affected rows exceeds this value, the SQL statement is considered at high risk.
Too many partitions involved	`max_partition_cnt >2000`	Too many partitions involved	The `max_partition_cnt` parameter specifies the maximum number of involved partitions allowed in an SQL statement. If the number of involved partitions exceeds this value, the SQL statement is considered at high risk.
Too many IN conditions	where id in (1,2,3,4,5.....)	Too many IN conditions	-
Index change	`drop index idx2 on test_high_risk2`	High-risk change - Index change	-

The High-risk SQL tab displays SQL Text, Database, Cause of Risk, and so on.

New SQL statements

New SQL statements are those that have not been executed for a period of time (7 days by default) before the query period but were executed within the query period.

The Add SQL tab displays SQL Text, SQL ID, Database, Total Executions, and so on.