TopSQL diagnostics

The TopSQL tab displays the SQL statements in descending order of the total response time in the tenant. On the Tenant Overview page, 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

You can diagnose top SQL statements by using the following two methods:

• Method 1: Log on to the OceanBase Cloud Platform (OCP) console. In the left-side navigation pane, click Autonomy Service. On the page that appears, select the cluster that you want to view to go to the Real-time Diagnostics page of the cluster.

  Note

  When you view top SQL statements by using Autonomy Service, the TopSQL feature allows you to perform real-time diagnostics on top SQL statements of a specific tenant or all tenants in a cluster. If you find that the CPU load of a host is unusually high, you can use this feature to identify the SQL statements that take the most time to execute on the host and optimize them.

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

The following procedure takes Method 1 as an example.

1. In the left-side navigation pane, click Autonomy Service. On the Cluster Details page, click the name of the cluster you want to view to go to the Real-time Diagnostics page of the cluster.

2. By default, the SQL Diagnostics tab 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 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 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.

   You can copy the SQL text and filter the SQL statements by database. You can also sort the SQL statements by the number of executions, total response time, average response time, error count, and plan generation time, and view the diagnosis result.

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

6. View TopSQL information.

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

      Note

      • The supported operators include the plus sign (+), minus sign (-), asterisk (*), forward slash (/), and parentheses.
      • After a custom column is added, the SQL statements on the **TopSQL** tab are automatically sorted by the custom column.
      • Only one custom expression can be created. If a custom column already exists, after you create a custom column, the new custom column overwrites the original one.
      • The attribute name of the custom column must start with @ or $.

      

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

      

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

      

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

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

   • 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 Set SQL throttling.

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