OceanBase Cloud Platform (OCP) can identify SQL statements that may cause risks to system performance, security, and data based on specific rules.
Applicability
This topic applies only to OCP Enterprise Edition. OCP Community Edition does not support this feature.
The following table describes the types of high-risk SQL statements that can be identified in OCP.
| Operation | Example | Risk type | Description |
|---|---|---|---|
| Column addition or deletion |
|
Schema change: Columns are added or deleted. | - |
| Table or database deletion |
|
A table or database is deleted. | - |
| Table truncation | truncate table test |
A data table is truncated. | - |
| Update without conditions | update test set new_id =1 |
An update without conditions is performed. | - |
| Update with a permanently true condition | update test set new_id =1 where 1 = 1 |
An update with a permanently true condition is performed. | - |
| Deletion without conditions | delete from test |
Deletion without conditions is performed. | - |
| Deletion with a permanently true condition | delete from test where 1=1 |
Deletion with a permanently true condition is performed. | - |
| Too many rows returned | max_return_rows > 50000 |
Too many rows are returned. | The max_return_rows parameter specifies the maximum number of returned rows allowed for an SQL statement. If the number of returned rows exceeds this value, the SQL statement is considered at high risk. You can modify the parameter as needed by referring to topics in the Manage system parameters chapter. |
| Too many rows affected | max_affected_rows > 50000 |
Too many rows are affected. | The max_affected_rows parameter specifies the maximum number of affected rows allowed for an SQL statement. If the number of affected rows exceeds this value, the SQL statement is considered at high risk. You can modify the parameter as needed by referring to topics in the Manage system parameters chapter. |
| Too many partitions involved | max_partition_cnt >2000 |
Too many partitions are involved. | The max_partition_cnt parameter specifies the maximum number of involved partitions allowed in an SQL statement. If the number of partitions exceeds this value, the SQL statement is considered at high risk. You can modify the parameter as needed by referring to topics in the Manage system parameters chapter. |
Prerequisites
You have enabled parameters in the SQL Diagnostics Collection section by referring to Manage parameters.
Procedure
Log on to the OCP console.
In the left-side navigation pane, click OceanBase Autonomy Service.
In the Cluster Details section, click the name of the cluster that you want to view.
The SQL Diagnostics tab of the Real-time Diagnostics page automatically appears.
Click the High Risk SQL tab.
Specify the filter conditions.
Time Range: You can select Last 5 Minutes, Last 10 Minutes, Last 20 Minutes, Last 30 Minutes, Last 1 Hour, Last 3 Hours, or Last 6 Hours from the Time Range drop-down list. You can also select Custom Time from the drop-down list and specify the start time and end time as needed. By default, the information of the last 6 hours is displayed.
OBServer: You can select an OBServer node or all OBServer nodes in the list. If you select an OBServer node, only SQL statements executed on the selected OBServer node are queried.
Internal SQL: If you select this option, the SQL statements internally initiated in OceanBase Database are displayed in the query result.
Keyword: The SQL statements that contain the specified keyword are displayed in the query result. The keywords filter SQL statements in the same way as the SQL LIKE operator. The entered strings are automatically prefixed and suffixed with a percent sign (%).
Advanced Search: You can add multiple filter conditions in Advanced Search. Click Add. In the Add Advanced Conditions dialog box, you can specify a metric, an operator, and a metric value. The SQL statements that match the specified conditions will be displayed in the query result.
Click Search to list all SQL statements that meet the search criteria. You can perform the following operations:
Click Export High Risk SQL to export all SQL statements in the query result.
View information about high-risk SQL statements.
On the High Risk SQL tab, you can view the columns selected. You can copy the SQL text and filter the SQL statements by database.
You can click the SQL text of an SQL statement to go to the SQL Details page of the statement, where you can view the following details of the SQL statement:
In the SQL Text section, you can view the complete SQL statement.
On the Previous Tendency tab, you can view the historical trends and plan generation time of the SQL statement. For more information, see the View the historical trends of an SQL statement section in View details of an SQL statement.
On the Execution Plans tab, you can view the execution plans of the SQL statement, or bind an execution plan to the statement. For more information, see the View the execution plans of an SQL statement section in View details of an SQL statement.
On the Index tab, you can view the indexes bound to the SQL statement. For more information, see the View and bind indexes section in View details of an SQL statement.
On the SQL Throttling tab, you can view or set throttling of the SQL statement. For more information, see the Set throttling for an SQL statement section in View details of an SQL statement.