SQL monitoring

OceanBase Cloud Platform (OCP) provides SQL monitoring features for suspicious SQL statements, TopSQL statements, SlowSQL statements, and ParallelSQL statements.

Background

Suspicious SQL statements, TopSQL statements, SlowSQL statements, and ParallelSQL statements are described as follows:

• Suspicious SQL statements: SQL statements that meet the diagnostic characteristics after a diagnosis based on the diagnosis type, SQL statement, execution history, and table structure.

• TopSQL statements: SQL statements that take the longest time to execute.

  The TopSQL feature sorts different types of SQL statements based on their execution time. The TopSQL query result reflects the performance of an OceanBase cluster, OceanBase Database tenant, or OBServer to execute different types of SQL statements in a period. You can locate poor-performing SQL statements in the database by using this feature.

• SlowSQL statements: SQL statements whose execution time exceeds the slow-query threshold. You can use the SlowSQL diagnosis feature to identify risky statements and avoid risks.

  The SlowSQL query result usually reflects the performance changes of an SQL statement in different periods. You can use the SlowSQL feature to locate the causes of the SQL performance changes.

• ParallelSQL statements: SQL statements that are configured with a degree of parallelism in a single execution.

  You can use ParallelSQL diagnostics to identify SQL statements that do not meet query performance expectations in analytical processing.

Suspicious SQL diagnostics

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 to go to the SQL Diagnostics page.

   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 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, 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 for 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. 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 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.

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

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

6. View the 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.
   • You can view the diagnostic results within a specific period of time. The following table describes some diagnoses and their suggested solutions.

You can search for parameters on the System Parameters page of OCP and modify their default values to define the objects to be diagnosed and the diagnostic criteria.

• On the Historical Trends tab, you can view the historical trends of the SQL statement.

• On the Execution Plans tab, you can view the execution plans of the SQL statement.

• On the Index tab, you can view the indexes bound to the SQL statement.

  OCP allows you to bind an index to an outline. You can bind an index to an SQL ID, so that OceanBase Database selects this index when it executes the SQL statement. At present, you can bind only one index to an SQL ID. For more information, see Plan binding.

  Note

  The outline feature relies on the hint and plan cache features of OceanBase Database. The hint feature determines the selection of the physical plan generation path by specifying the index and the connection method. The plan cache caches physical plans for SQL statements. When the same SQL statement is executed again in the same environment, the system reads its execution plan from the plan cache. It does not have to go through the parser, resolver, rewriter, and optimizer modules again. This speeds up the execution of SQL statements. The outline feature uses hints to specify how to fix plans by modifying the physical execution plan in the plan cache.

• On the SQL Throttling tab, you can view or set the throttling of the SQL statement.

• To view the binding records of the SQL statement, click the button 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 also click Bind to bind the plan to the SQL statement again.

7. Set throttling.

   • Click Enable Throttling to throttle the SQL statement.

   • Select multiple SQL statements and click Batch Set Throttling. In the dialog box that appears, specify the maximum number of concurrent threads for executing the SQL statements.

   Note

   Keyword-based throttling is not supported in batch throttling.

TopSQL diagnostics

Top SQL statements are SQL statements that take the longest time to execute. On the Tenants page of OCP, you can filter top SQL statements by time range and OBServer node and view the details of each top SQL statement in the list.

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 to go to the SQL Diagnostics page.

   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 TopSQL tab.

4. Filter the TopSQL 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 for the last 30 minutes 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.

      • 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 TopSQL to export all the SQL statements in the query result.

6. View TopSQL 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.

   • Click Column Management. In the dialog box that appears, select the columns to display. Then, you can view the selected columns in the TopSQL list.

   • Click the + icon on the left side of the SQL text to view the specific error details of the SQL statement.

   • You can click the SQL text to go to the corresponding SQL text page.

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

     • On the Historical Trends tab, you can view the historical trends of the 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.

     • On the Index tab, you can view the indexes bound to the SQL statement.

       OCP allows you to bind an index to an outline. You can bind an index to an SQL ID, so that OceanBase Database selects this index when it executes the SQL statement. At present, you can bind only one index to an SQL ID. For more information, see Plan binding.

       Note

       The outline feature relies on the hint and plan cache features of OceanBase Database. The hint feature determines the selection of the physical plan generation path by specifying the index and the connection method. The plan cache caches physical plans for SQL statements. When the same SQL statement is executed again in the same environment, the system reads its execution plan from the plan cache. It does not have to go through the parser, resolver, rewriter, and optimizer modules again. This speeds up the execution of SQL statements. The outline feature uses hints to specify how to fix plans by modifying the physical execution plan in the plan cache.

     • On the SQL Throttling tab, you can view or set the throttling of the SQL statement.

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

7. Set throttling.

   • Click Enable Throttling to throttle the SQL statement.

   • Select multiple SQL statements and click Batch Set Throttling. In the dialog box that appears, specify the maximum number of concurrent threads for executing the SQL statements.

   Note

   Keyword-based throttling is not supported in batch throttling.

SlowSQL diagnostics

Before SlowSQL diagnosis, you can set a regular execution time for SQL statements. By default, when the execution time of an SQL statement reaches 100 ms, the SQL statement is considered a slow SQL statement. You can perform the following steps to change the default slow SQL threshold:

1. Execute the following SQL statements in a command-line tool or OceanBase Developer Center (ODC) to change the default slow SQL 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 = 12000000 where collection_name = 'slow_sql';
   

2. Change the value of the 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 to go to the SQL Diagnostics tab.

   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 for the last 30 minutes 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.

      • 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 to export all the 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 TopSQL list.

   2. On the SlowSQL tab, you can view the columns selected. You can also copy the SQL text, filter it by database and user, and sort it 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.

      • In the SQL Sampling section, you can view the details of the slow SQL statement. You can view the trace ID, 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, or a custom time range. You can copy the trace ID. You can also sort the SQL statements by request time, response time, CPU time, plan execution time, and number of threads for executing the SQL statement.

      • On the Historical Trends tab, you can view the historical trends of the 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.

      • On the Index tab, you can view the indexes bound to the SQL statement.

        OCP allows you to bind an index to an outline. You can bind an index to an SQL ID, so that OceanBase Database selects this index when it executes the SQL statement. At present, you can bind only one index to an SQL ID. For more information, see Plan binding.

        Note

        The outline feature relies on the hint and plan cache features of OceanBase Database. The hint feature determines the selection of the physical plan generation path by specifying the index and the connection method. The plan cache caches physical plans for SQL statements. When the same SQL statement is executed again in the same environment, the system reads its execution plan from the plan cache. It does not have to go through the parser, resolver, rewriter, and optimizer modules again. This speeds up the execution of SQL statements. The outline feature uses hints to specify how to fix plans by modifying the physical execution plan in the plan cache.

      • On the SQL Throttling tab, you can view or set the throttling of the SQL statement.

      • To view the binding records of the SQL statement, click the button 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.

   • Click Enable Throttling to throttle the SQL statement.

   • Select multiple SQL statements and click Batch Set Throttling. In the dialog box that appears, specify the maximum number of concurrent threads for executing the SQL statements.

   Note

   Keyword-based throttling is not supported in batch throttling.

ParallelSQL diagnostics

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 to go to the SQL Diagnostics tab.

   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 ParallelSQL tab.

4. Filter ParallelSQL 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 for the last 30 minutes 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. Click Add. In the Add Advanced Conditions panel, you can specify a metric, an operator, and a metric value.

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

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

6. View information about ParallelSQL 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 ParallelSQL list.

   2. On the ParallelSQL tab, you can view the columns selected. You can copy the SQL text and filter the SQL statements by database. You can also sort the SQL statements by the degree of parallelism, number of executions, total response time, and average response time. You can also view the diagnosis result.

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

      • In the SQL Execution Profile section, you can view the execution details of the SQL statement at the operator level in the data collection time range.

      • On the Historical Trends tab, you can view the historical trends of the SQL statement and the generation time of the corresponding plan.

      • On the Execution Plans tab, you can view the execution plans of the SQL statement, or bind an execution plan to the statement.

      • On the Index tab, you can view the indexes bound to the SQL statement.

      • On the SQL Throttling tab, you can view or set the throttling of the SQL statement.

      • You can view the binding records of the SQL statement 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.

   • Click Enable Throttling to throttle the SQL statement.

   • Select multiple SQL statements and click Batch Set Throttling. In the dialog box that appears, specify the maximum number of concurrent threads for executing the SQL statements.

   Note

   Keyword-based throttling is not supported in batch throttling.