Top SQL refers to SQL statements that are executed frequently, consume a large amount of system resources, and have a significant impact on database performance. In OB Cloud, you can view the top 2,000 SQL statements based on total execution time.
Query the list of top SQL statements
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 workspace.
In the left-side navigation pane, click Tenants.
In the tenant list, click the name of the target tenant to go to the tenant workspace.
In the left-side navigation pane, click Diagnostics.
On the Diagnostics page, the SQL tab is displayed by default. On this tab, the Top SQL section is displayed.
(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:
- The maximum time range cannot exceed 24 hours.
- The minimum time range is as follows:
- Within 3 days: The time range must be at least 1 minute.
- Between 3 and 7 days: The time range must be at least 5 minutes.
SQL ID: Enter the SQL ID to be queried. You can enter multiple IDs and separate them with half-width characters , or ;.
SQL text: Enter the SQL text keyword to be queried. The query results will display SQL statements whose text contains the specified keyword. Constant search is not supported.
More Conditions: Click +Add and configure the metric, operator, and metric value in the Add More Conditions panel. During the query, the SQL statements that meet the metric conditions will be displayed in the query result list.
Quick Filter: You can select Full Table Scan, Multi-partition scan, Multiple Remote Executions, Hard Parsing, Error Executions, or 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 the IN clause will be aggregated without affecting the statistics. In the list, you can see the Aggregated identifier. 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 whose contents are composed of different numbers of simple constants, if the Aggregate In Query option is not enabled, the real-time diagnostics feature will treat them as different types of SQL statements. If the Aggregate In Query option is enabled, the real-time diagnostics feature will treat them as the same type of SQL statements and aggregate their statistics into the same row.
Click ... and then click Download Table to download the list of top SQL statements.
Click the
icon 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 to compare the execution status and performance of the same SQL statement in different time periods on the same host node or on different host nodes.
Comparison of different periods: Compares the execution status of the same SQL statement in the baseline period and the comparison period on the same host node. You can customize both the baseline period and the comparison period. After you select 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 in different time periods on the same host node.
Comparison of different nodes: Compares the execution status of the same SQL statement on other nodes with that on the base node. You can select multiple comparison nodes. If you select multiple comparison nodes, the system compares the average data of the SQL statements executed on the selected nodes with that on the base node. After you select or customize an analysis period, click Comparative Analysis to view the Comparison of Details and Comparison of the trend of execution time proportion information of the same SQL statement on different nodes.