SlowSQL diagnostics

Slow SQL statements are statements whose execution time exceeds 100 ms. You can use the SlowSQL diagnostics feature to identify risky statements and avoid risks.

Set the execution time threshold that defines a slow SQL statement

By default, an SQL statement is considered a slow SQL statement if its execution time exceeds 100 ms. You can perform the following steps to modify the default threshold that defines a slow SQL statement:

1. Execute the following SQL statements in a command-line tool or OceanBase Developer Center (ODC) to change the default slow SQL statement threshold to 120000 μs.

   select collect_elapsed_threshold_us from ob_agent_collection_config_history 
   where collection_name = 'slow_sql';
   update ob_agent_collection_config_history set collect_elapsed_threshold_us = 120000 where collection_name = 'slow_sql';
   

2. Change the value of the OceanBase Cloud Platform (OCP) system parameter ocp.ob.slowsql.threshold to 120000 μs.

Procedure

1. In the left-side navigation pane, click Tenants and click the target tenant in the Tenants list to go to the Overview page of the tenant.

2. In the left-side navigation pane, click SQL Diagnostics . The SQL Diagnostics page appears.

   The SQL diagnostic data is not displayed on the SQL Diagnostics page 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 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, 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.

      • OBServer: You can select an OBServer or All OBServers in the list. If you select an OBServer, only SQL statements executed on the selected OBServer 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.

      • 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 criteria are displayed in the query result.

      

   2. Click Search to list all SQL statements that meet the search criteria.

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

6. View the information of 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 SlowSQL list.

      

   2. On the SlowSQL tab, you can view the columns selected. You can copy the SQL text of an SQL statement, 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.

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

      • In the SQL Details section, you can view the details of the slow SQL statement. You can select Last 5 Minutes, Last 10 Minutes, Last 20 Minutes, Last 30 Minutes, Last 1 Hour, or Last 3 Hours, or select Custom Time and specify a time range to view details of the SQL statement, such as the trace ID, request time, response time, execution time, physical reads, application latency, returned rows, server IP address, and client IP address. You can copy the trace ID. You can also sort the SQL traces by request time, response time, execution time, physical reads, application latency, and returned rows.

      • 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](5.view-sql-details.md).

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