Top SQL statements are those that are executed frequently, consume a large amount of system resources, and significantly impact database performance. In the ApsaraDB for OceanBase Cloud console, you can view the top 2,000 SQL statements sorted by total execution time.
Query the Top SQL list
Log in to the OceanBase Cloud console.
In the left-side navigation pane, click Instances.
In the instance list, find the target instance, click the instance name, and go to the instance overview page.
In the left-side navigation pane, click Tenants.
In the tenant list, click the name of the target tenant to go to the tenant overview page.
In the left-side navigation pane, click Diagnostics.
On the Diagnostics page, the Top SQL tab under the SQL section is displayed by default.
(Optional) Configure the filter conditions.
Database: Select a database in the current tenant.
Node: Select a node in the current tenant.
Time range: Select a time range. You can also customize the time range (up to 7 days). Note the following limitations:
- Maximum range: The selected time range must not exceed 24 hours.
- Minimum range:
- Within 3 days: The selected time range must be at least 1 minute.
- Between 3 and 7 days: The selected time range must be at least 5 minutes.
SQL ID: Enter the SQL ID to be queried. You can enter multiple IDs, separated by half-width characters , or ;.
SQL text: Enter the SQL text keyword to be queried. The query results will display SQL text containing the keyword. Constant search is not supported.
More Conditions: Click +Add, and in the Add More Conditions panel, configure the metrics, operators, and metric values. During the query, the SQL statements that meet the conditions will be displayed in the query result list.
Quick Filter: Supports Full Table Scan, Multi-partition scan, Multiple Remote Executions, Hard Parsing, Error Executions, and Retry execution.
- Click Query.
Click + before the SQL text to view Last error and Summary of errors reported during statistical periods.
Filter the SQL text by Database.
In the Actions column, click View samples to view the SQL sample.
- If you enable Aggregation In Query, the results of in queries will be aggregated, but the statistics will remain unchanged. In the list, you can see the Aggregated label. Hover the pointer over the corresponding SQL ID to view other SQL IDs that are aggregated.
Note
For SQL statements that contain IN clauses and have different numbers of simple constants, if you do not enable the Aggregate in Query feature, the real-time diagnosis feature will treat them as different types of SQL statements. If you enable the Aggregate in Query feature, the real-time diagnosis feature will treat them as the same type of SQL statements and aggregate the statistics of these SQL statements into the same row.
Click the download icon to download the Top SQL list.
Click
to query more metrics in the Manage Columns window.
Compare Top SQL
On the Top SQL tab, click Top SQL Comparison in the upper-right corner of the list. This action allows you to compare the execution status and performance of the same SQL statement at different time periods on the same host node or across different host nodes.
Comparison of different periods: This option compares the execution status of the same SQL statement on the same host node during the baseline period and the comparison period. You can customize both the baseline period and the comparison period. After selecting the analysis node for comparison, click Comparative Analysis to view the Comparison of Details and Comparison of response time trends information of the SQL statement on the same host node during different time periods.
Comparison of different nodes: This option compares the execution status of the same SQL statement on other host nodes with that on the base node. You can select multiple comparison nodes. If you select multiple nodes, the system will compare the average data of the intersection of SQL statements executed on these nodes with the SQL statement on the base node. After selecting or customizing the analysis period, click Comparative Analysis to view the Comparison of Details and Comparison of the trend of execution time proportion information of the SQL statement on different host nodes.
View optimization suggestions and optimization detection history
On the Top SQL tab, click View Optimization Recommendation in the upper-right corner of the list. In the panel that appears, view the QPS, Average SQL Statement Processing Time, CPU Usage, and other metrics of the tenant, as well as optimization suggestions for SQL statements and schema.
Click Initiate detection in the upper-right corner. In the window that appears, select Detection Time Range (which cannot exceed 1 day) and click OK.
On the Optimize the detection history tab, view the task status and optimization detection history.