Diagnose slow SQL statements

Slow SQL statements are statements whose execution time exceeds the specified threshold, which is 100 ms by default. You can use the slow SQL diagnostics feature to identify risky statements and avoid risks.

You can diagnose slow SQL statements by using one of 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 slow 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 slow 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.

• By default, an SQL statement whose execution time exceeds 100 ms is considered a slow SQL statement. You can choose Cluster > O&M Configuration > SQL Diagnostics Collection , find the monagent.ob.slow.sql.threshold parameter, and change its value to modify the default threshold that defines a slow SQL statement. For more information, see Manage parameters.

Procedure

The procedure of Method 1 is described as follows:

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

4. Filter the slow 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.

   • OBServer: You can select one OBServer node or all OBServer nodes in the list. If you select one 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 : If you specify a keyword, the SQL statements that contain the keyword are displayed in the query result.

   • Advanced Search: You can select metrics such as SQL ID, Executions , or Executions per Second from the drop-down list and specify the value range. The SQL statements that match the specified conditions are displayed in the query result.

   • Quick Filter : You can select an option, such as Full Table Scan or Multi-Partition Scan , to quickly identify SQL statements that need to be optimized.

   

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

5. Click Export SlowSQL to export all SQL statements in the query result.

6. View information about slow SQL statements.

   1. Click Column Management . In the dialog box that appears, select the columns to display. Then, you can view the selected columns in the slow SQL statement list.

      

   2. On the SlowSQL tab, you can view the columns selected. You can copy the SQL text of an SQL statement, view and copy the sample, filter the SQL statements by database and user, and sort the SQL statements by the number of executions, total response time, response time, and CPU time.

      

   3. 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. You can view and copy the sample.

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

      • In the SQL Sampling section, you can view the details of the slow SQL statement. You can view the original SQL text, request time, request IP address, database, user, response time, CPU time, plan execution time, and number of threads for executing the SQL statement in the last 5 minutes, last 10 minutes, last 20 minutes, last 30 minutes, last 1 hour, last 3 hours, last 6 hours, or a custom time range. You can copy the original SQL text. You can also sort the SQL statements by the request time, response time, CPU time, plan execution time, and number of threads for executing the SQL statement.

        Note

        You can view traces only if you use OceanBase Database V4.0.0 or later and the end-to-end tracing feature is enabled.

        

        You can click View Trace in the Actions column to view the trace of the SQL statement. For more information about how to enable end-to-end tracing, see Configure full link tracking for a tenant.

        

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

   Note

   Keyword-based throttling is not supported in batch throttling.