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:

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.

Filter the SQL diagnostic information by Database, Node, Duration, SQL ID, SQL Text, 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 Add to add more conditions.
Click the icon for a column to sort the list by the column.
Click Manage Columns 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.

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 to quickly locate SQL statements that need to be optimized.
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 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 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.
You can diagnose the TopSQL of a specified tenant or all tenants in a cluster.
When the CPU utilization of a host is too high, you can filter the TopSQL 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.

You can perform the following operations related to SlowSQL:
- 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.
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

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

The following table describes the types of high-risk SQL 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

New SQL refers to SQL statements that have not been executed for a period of time (7 days by default) before the query interval, but have execution records within the query interval.

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