Slow SQL refers to an SQL statement whose response time exceeds 500 ms.
Create a slow SQL alert
Log in to the OceanBase Cloud console.
Click Alerts in the left-side navigation pane.
On the Alert Management page, click Alert Rules.
On the Alert Rules tab, click Create Alert Rule.
Configure the alert rule.
Specify the following parameters:
Parameter Description Rule Name Specify the name of the alert rule, such as oas_test. Alert Object Type Select Tenant as the alert object. Select Instance Select the target instance. Select Tenant Select the target tenant. Alert Type Select Metric as the alert type. Configuration Method Select Custom as the configuration method. Alert Trigger Condition Select Meet All Conditions as the alert trigger condition. Alert Metric - Select Slow SQL Count in the Alert Metric drop-down box.
- Select Maximum in the Comparison Value drop-down box.
- Select >= in the Operator drop-down box.
- Enter 0 in the Threshold text box.
Alert Cycle Select the alert detection cycle, such as 1 Consecutive Cycle (1 Cycle = 1 Minute). Alert Frequency Select the alert trigger frequency, such as Alert Only Once. Set the notifications.
Parameter Description Alert Level Select Info, Warning, or Critical. Alert Method The default is Email + Webhook. Alert Notification Recipient You can select Alert Contact Group or Alert Contact. Effective Time of Rule An alert is triggered only during the effective time of the configured rule.
Click Create.
View the list of Slow 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 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.
On the SQL tab, select Slow SQL.
(Optional) Configure filter conditions.
Database: Select any database in the current cluster.
Node: Select any node in the current database.
Time Range: View the data of the Last 5 Minutes, Last 30 Minutes, Last 1 Hour, Last 3 Hours, 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 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 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 Slow SQL tab displays the following metrics by default: SQL text, Tenant Name, SQL ID, Database, Total Executions, Total Database Time (ms), Average Time (ms), Maximum Response Time (ms), CPU time (ms), Plan Generation Time (ms), Maximum Rows Returned, Rows of Memory Reads, Rows of Physical Reads, and Actions.
You can filter SQL statements by Tenant Name and Database.
You can sort SQL statements by Total Executions, Total Database Time (ms), Average Time (ms), Maximum Response Time (ms), CPU time (ms), Plan Generation Time (ms), Maximum Rows Returned, Rows of Memory Reads, and Rows of Physical Reads.
In the Actions column, click View samples to view SQL samples.
In the Actions column of a SQL statement, click ..., and then select Set Throttling to set throttling for the SQL statement. You can select multiple SQL statements to set throttling in bulk.
Click the checkbox in the upper-right corner of the list to select all SQL statements or select specific SQL statements. Then, click Aggregation In Query.
Note
Aggregate In Query means that the number of constant parameters in the in clause of the SQL statement is ignored, and each in clause is treated as having only one constant parameter. The SQL statements are then parameterized and the SQL ID is calculated based on the parameterized SQL statements. SQL statements with the same SQL ID are grouped into one.
Choose ... > Download Table to download the Slow SQL statement list.
Choose ... > Modify Slow SQL Threshold. In the dialog box that appears, modify the Slow SQL threshold and click OK.
Click the icon
to query more metrics.