OCP support view the SQL statements that are identified as risky for system performance, security, and data according to rules.
The following table describes the types of high-risk SQL statements that can be identified in OCP.
| Operation | Example | Risk type | Description |
|---|---|---|---|
| Add or delete a column |
|
Schema change - Columns are added or deleted. | - |
| Delete a table or database |
|
A table or database is deleted. | - |
| Empty a table | truncate table test |
A data table is cleared. | - |
| 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. | - |
| Delete without conditions | delete from test |
Deletion without conditions is performed. | - |
| Delete 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. For more information, see Manage system parameters. |
| 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. For more information, see Manage system parameters. |
| 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. For more information, see Manage system parameters. |
Procedure
Log on to the OceanBase Cloud Platform (OCP) console.
In the left-side navigation pane, click Autonomous service.
In the Cluster Details section, click the name of the target cluster.
By default, the SQL Diagnostics tab of the Real-time Diagnostics page appears.
Click the High Risk SQL tab.
Specify the filter conditions, filter the High Risk SQL statements.
Time Range: You can select Last 5 Minutes, Last 10 Minutes, Last 20 Minutes, Last 30 Minutes, Last 1 Hour, or Last 3 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 30 minutes is displayed.
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.
Advanced Search: You can add multiple filter conditions in Advanced Search . Click Add . In the Add Advanced Condition dialog box, you can specify a metric, an operator, and a metric value. The SQL statements that match the specified criteria 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 on the query results:
Click Export TopSQL to export all the SQL statements in the query result.
View High Risk SQL information.
Click Custom Column . In the dialog box that appears, specify the expression and name for the custom column. Then, you can view the column in the TopSQL list.
You can click the SQL text of an SQL statement to go to the SQL Details page of the statement.
On the SQL Details page, 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 of the SQL statement. For more information, see View the historical trends 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 View the execution plans of an SQL statement.
On the Index tab, you can view the indexes bound to the SQL statement. For more information, see View and bind indexes.
On the SQL Throttling tab, you can view or set throttling of the SQL statement. For more information, see Set SQL throttling.