Diagnose suspicious SQL statements

Suspicious SQL statements are statements that meet specific diagnostic criteria based on the diagnostic type, SQL statement, execution history, and table schema. You can handle suspicious SQL statements based on the suggestions given.

You can diagnose suspicious SQL statements by using the following methods:

• Method 1: Log in to the OceanBase Cloud Platform (OCP) console. In the left-side navigation pane, click OceanBase Autonomy Service . On the page that appears, find the target cluster and click its name to go to the Real-time Diagnostics page.

• Method 2: Log in to the OCP console. On the Overview page of a tenant, click SQL Diagnostics in the left-side navigation pane.

Prerequisites

• To diagnose suspicious SQL statements by using Method 1, make sure that you have the following permissions:

  • Resource Permissions : Cluster Read-only or Tenant Read-only permission
  • Menu Permissions : Permission on the Real-time Diagnostics menu of OceanBase Autonomy Service

• To diagnose suspicious SQL statements by using Method 2, make sure that you have the following permissions:

  • Resource Permissions : Cluster Read-only or Tenant Read-only permission
  • Menu Permissions : Permission on the SQL Diagnostics menu of Tenants

• You have enabled parameters in the SQL Diagnostics Collection section by referring to Manage parameters.

Procedure

The procedure of Method 1 is described as follows:

1. Log in to the OCP console. In the left-side navigation pane, click OceanBase Autonomy Service . On the Cluster Details page, click the name of the target cluster to go to its Real-time Diagnostics page.

2. By default, the SQL Diagnostics tab appears.

   The SQL diagnostic data is not displayed on the SQL Diagnostics tab if you do not set the values of both the cluster parameter enable_sql_audit and the tenant parameter ob_enable_sql_audit to True. You can click Change Cluster Parameters in the prompt to modify the parameter values.

   

3. Click the Suspected SQL tab.

4. Filter the suspicious SQL statements.

   1. 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.

   • Internal SQL : If you select this option, the SQL statements internally initiated in OceanBase Database are displayed in the query result.

   • Keyword : If you specify a keyword, the SQL statements that contain the 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 are displayed in the query result.

   2. Click Search to list all SQL statements that meet the filter conditions.

   You can copy the SQL text and view the diagnostic result. You can filter the suspicious SQL statements by database and sort them by the number of executions, average response time, average CPU time, and plan generation time.

   

5. Click Export Suspicious SQL Statements to export all suspicious SQL statements in the query result.

6. View information about suspicious SQL statements.

   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.

   • In the Optimization Suggestions section, you can view the optimization suggestions for the SQL statement. For more information, see the View optimization suggestions section in View the details of an SQL statement.

   • View diagnostic details: You can view the diagnostic results of a specific period of time.

   The following table describes some diagnostic types and the suggested solutions.

   Diagnostic type	Related parameter	Index analysis required	SQL risk level	Description and suggestion
   Plan change accompanied with performance degradation	ocp.perf.sql-diag.performance-degradation-after-plan-changed-config	No	High	The execution plan of the SQL statement was changed, and the performance of the new plan was degraded. Check with the DBA. You can use an outline to fix the execution plan to verify whether the SQL statement is problematic.
   Table scan without available index	ocp.perf.sql-diag.table-scan-index-not-exists-config	Yes	High	During the execution of the SQL statement, a full table scan was performed on some related tables because they did not have indexes available. We recommend that you create appropriate indexes.
   Table scan with unused index	ocp.perf.sql-diag.table-scan-index-not-exists-config	Yes	High	All tables involved in the execution of the SQL statement are indexed. However, a full table scan was performed on some of these tables without using their indexes. Check the data distribution and business scenario.
   Hint ineffective	ocp.perf.sql-diag.ineffective-hint-config	Yes	High	An index was specified in the hint of the SQL statement, but the specified index was not used during execution. Check whether the hint of the SQL statement matches the actual execution plan.
   Performance degradation	ocp.perf.sql-diag.performance-degradation-config	No	Medium	During the diagnosis period, the average CPU time consumed for the execution of the SQL statement is longer than the historical average. Check the changes in data distribution and the queue in the tenant.
   Poor performance despite the use of index	ocp.perf.sql-diag.awful-performance-index-used-config	Yes	Medium	The index was used for table access during the execution of the SQL statement, but the performance was poor. Check the data distribution and business scenario.
   Execution spikes	ocp.perf.sql-diag.execution-spike-config	No	Medium	The executions of the SQL statement suddenly increased during a period of time. You can view the number of executions on the Previous Tendency tab of the SQL Details page. Check the business volume. A spike in the number of executions of an SQL statement may cause the overall performance to decrease.
   Row lock contention	ocp.perf.sql-diag.row-lock-contention-high-config	No	Medium	Check your business scenarios, whether the SQL statements of the select for update type were executed more frequently than the specified value, and whether the CPU time was greater than the specified value.
   CPU utilization above threshold	ocp.perf.sql-diag.cpu-time-proportion-high-config	No	Low	The CPU utilization is too high during the diagnosis period. CPU utilization = CPU time for executing the SQL statement/CPU time for executing all SQL statements of the tenant × 100%. Check your business scenarios, data distribution changes, request increases, and execution plan changes.

   By default, SQL diagnostics is triggered in OCP only when some attributes such as the SQL response time and execution frequency exceed the specified thresholds. You can modify the default values of specific parameters on the System Parameters page of OCP, to specify the objects and criteria for diagnostics. For information about the parameters, see the SQL performance module section in SQL performance diagnosis related parameter configuration.

   • On the Previous Tendency tab, you can view the historical trends of the SQL statement. For more information, see the View the historical trends of an SQL statement section in View the 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 the 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 the details of an SQL statement.

   • On the SQL Throttling tab, you can view or set throttling for the SQL statement. For more information, see the Set throttling for the SQL statement section in View the details of an SQL statement.

   • You can view the binding records of the SQL statement in section ① as illustrated on the Execution Plans , Index , and SQL Throttling tabs.

   In the binding records, you can view the status of a bound execution plan, or click Unbind to unbind the plan from the SQL statement. You can click Bind Plan to bind the plan to the SQL statement again.

   

7. Set throttling.

   • You can click Enable Throttling to enable throttling for the SQL statement. For more information, see the Set throttling for the SQL statement section in View the details of an SQL statement.

   • You can also select multiple SQL statements and click Batch Set Throttling . In the dialog box that appears, specify the maximum number of SQL statements that can be executed concurrently.