Top SQL refers to SQL statements in the database that are executed frequently, consume a significant amount of system resources, and have a considerable impact on database performance. OceanBase Cloud shows you the top 2,000 SQL statements sorted by total execution time.
View 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 Overview page of the instance.
Click Diagnostics in the left-side navigation pane, and then select the Real-Time Diagnostics tab on the Diagnostics page. The system displays the Top SQL sub-tab on the SQL tab by default.
(Optional) Configure filter conditions.
Database: Select any database in the current cluster.
Time Range: View the data of the Last 5 Minutes, Last 30 Minutes, Last 1 Hour, or Last 6 Hours. You can also customize the time range (up to 15 days), but please 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.
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 displays the SQL containing the keyword in the SQL text by default. Constant search is not supported.
More Conditions: Click +Add, and in the Add More Conditions panel, configure the metric, operator, and value. When querying, the SQL that satisfies the metric conditions will be displayed in the query result list.
Quick Filter: You can quickly filter SQLs by Full Table Scan, Multi-partition Scan, Multiple Remote Executions, Hard Parsing, Error Executions, or Retry Executions.
Click Query.
The Top SQL tab displays the following metrics by default: SQL Text, SQL ID, Database, Total Executions, Total Errors, Maximum Response Time (ms), CPU Time (ms), Plan Generation Time (ms), and Actions.
Click + before the SQL text to view Last error and Summary of errors reported during statistical periods.
Filter the SQL text by Database.
Sort the SQL text by Total Executions, Total Errors, Maximum Response Time (ms), CPU Time (ms), and Plan Generation Time (ms).
In the Actions column, click View Sample to view the SQL sample.
If you enable Aggregate In Query, the results of IN queries will be aggregated 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.
Note
For SQL statements that contain IN clauses and whose contents consist of different numbers of simple constants, if Aggregate In Query is not enabled, the real-time diagnostics feature treats them as different types of SQL statements; if Aggregate In Query is enabled, the real-time diagnostics feature treats them as the same type of SQL statements and aggregates the statistics of these SQL statements into the same row.
Click ..., and then click Download Table to download the Top SQL list.
Click the icon
to query more metrics in the Manage Columns window that pops up.
Compare Top SQL
On the Top SQL tab, click Top SQL 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: Compare the running status of the same SQL on the same node in the baseline period and the comparison period. Both the baseline period and the comparison period can be customized. Select the analysis node to be compared and click Comparative Analysis to view the Comparison of Details and Comparison of Response Time Trends.
Comparison of Different Nodes: Compare the running status of an SQL statement on the baseline node with that of the same SQL statement on other nodes. You can select multiple comparison nodes. If you select multiple nodes, the system will take the average data of the intersection of the SQL running on these nodes and compare it with the same SQL on the baseline node for analysis. After you specify an analysis period, click Comparative Analysis to view the Comparison of Details and Comparison of the trend of execution time proportion.