Top SQL refers to SQL statements that are executed frequently, consume a large amount of system resources, and significantly impact database performance. In OceanBase Cloud, 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 workspace.
In the left-side navigation pane, click Tenants.
In the tenant management 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 Top SQL tab is displayed by default under the SQL tab.
(Optional) Configure the filter conditions.
Database: Select any database under the current tenant.
Node: Select any node under the current tenant.
Time range: Select a time range. You can also customize the time range (up to 7 days). However, the following limitations apply:
- Maximum range: The selected time range must not exceed 24 hours.
- Minimum range:
- For the last 3 days: The selected time range must be at least 1 minute.
- For 3 to 7 days: The selected time range must be at least 5 minutes.
SQL ID: Enter the SQL ID to query. You can enter multiple IDs, separated by half-width characters , or ;.
SQL text: Enter the SQL text keyword to query. The query results will display SQLs containing this keyword in the SQL text. Constant search is not supported.
More Conditions: Click +Add and, in the Add More Conditions panel, configure the metric, operator, and metric value. The query results will display SQLs that meet the conditions.
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. You can move the pointer over the SQL ID with the Aggregated label to view other SQL IDs that are aggregated.
Note
For SQL statements that contain IN clauses and whose content consists of different numbers of simple constants, if the Aggregate In Query feature is not enabled, the real-time diagnosis feature will treat them as different types of SQL statements; if the Aggregate In Query feature is enabled, the real-time diagnosis 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 Compare in the upper-right corner of the list to compare the execution status and performance of the same SQL 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 on the same host node in the baseline period and the comparison period. You can customize the baseline period and the comparison period. Select the analysis node to be compared, and click Comparative Analysis to view the Comparison of Details and Comparison of response time trends information of the SQL execution in different time periods on the same host node.
Comparison of different nodes: Compares the execution status of the same SQL 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 execution on these 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 execution on different nodes.