Top SQL statements are those that are frequently executed and consume a large amount of system resources, and thus significantly affect database performance. OceanBase Cloud displays the SQL statements that rank in the top 2,000 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 Diagnostics page. Click the Real-Time Diagnostics tab. The system displays the SQL tab by default, and then the Top SQL tab.
(Optional) Configure filter conditions.
Database: Select any database in the current cluster.
Time range: Supports viewing data in the following time ranges: Past 5 Minutes, Past 30 Minutes, Past 1 Hour, Past 3 Hours, and Past 6 Hour(s). You can also customize the time range (up to 15 days), 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.
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 the SQL containing the 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 value. When you query, the SQL that meets the metric conditions is displayed in the query result list.
Quick Filter: Supports the following options: Full Table Scan, Multi-partition scan, Multiple Remote Executions, Hard Parsing, Error Executions, and Retry execution.
Click Query.
The Top SQL tab displays the following metrics by default: SQL text, SQL ID, Database, Total Executions, CPU Percentage (%), Total Database Time (ms), Average Time (ms), 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, CPU Percentage (%), Total Database Time (ms), Average Time (ms), Total Errors, Maximum Response Time (ms), CPU Time (ms), and Plan Generation Time (ms).
In the Actions column, click View samples to view SQL samples.
In the Actions column of a SQL, click ... and select Set Throttling to set throttling for the SQL. You can select multiple SQL statements for batch throttling settings.
Click the right-side icon in the upper part of the list to display the SQL statements in aggregated in queries, without affecting the statistics. You can select multiple SQL statements, including those in aggregated in queries, for batch throttling settings.
Note
Aggregated in query means that the SQL ID is calculated based on the number of constant parameters in the IN clause, regardless of the actual number of constant parameters in the IN clause. Then, the SQL statements are parameterized according to this rule, and the SQL statements with the same SQL ID are aggregated.
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.
View optimization suggestions and 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 for the specified tenant, as well as the optimization suggestions for SQL and schema classes.
- Click Initiate detection in the upper-right corner. In the window that appears, select the 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.
