All Products
Search
Document Center

ApsaraDB for OceanBase:View details of an SQL statement

Last Updated:Mar 25, 2024

The SQL Details page displays the text, optimization suggestions, sampling details, diagnostic details, sampling history, execution history, and execution plans of an SQL statement. The displayed information varies based on the diagnostic situation of the specific SQL statement.

SQL details

On the SQL Diagnostics page, you can view the basic information about an SQL statement and click the SQL statement to go to its details page. The details page displays the text, optimization suggestions, sampling details, diagnostic details, sampling history, execution history, and execution plans of the SQL statement.

SQL text

In the SQL text section, you can view the complete SQL text and select Table Structure to synchronize or view the schema of the corresponding table.

image

Optimization suggestions

Note

This section is not displayed if no optimization suggestion is provided.

In the Optimization Suggestions section, you can view all errors of the SQL statement within the diagnostics time range and corresponding optimization suggestions. You can take actions based on the optimization suggestions provided by the system. Two types of optimization suggestions are provided:

  • Plan recommendation: The system analyzes the execution plans for the SQL statement within the last seven days and recommends the one that outperforms others (local plans or distributed plans) in terms of CPU time. If an execution plan is recommended, we recommend that you analyze the differences between the recommended execution plan and the existing execution plan. If the recommended execution plan does not contain DDL operations for changes, you can bind it. After you bind the recommended execution plan, check the optimization effects. If the execution is not optimized, perform a rollback.

    Plan recommendation rules: A candidate plan is recommended based on the CPU time of the latest plan. When the CPU time of the latest plan is short, a candidate plan is recommended only when its CPU time is significantly shorter than that of the latest plan. When the CPU time of the latest plan is long, a candidate plan is recommended only when its CPU time is slightly shorter than that of the latest plan.

    CPU time of the latest plan

    CPU time of the candidate plan

    [0, 1) ms

    The CPU time of the candidate plan is 1/10 of that of the latest plan.

    [1, 10) ms

    The CPU time of the candidate plan is 1/5 of that of the latest plan.

    [10, 100) ms

    The CPU time of the candidate plan is 1/2 of that of the latest plan.

    [100, 1000) ms

    The CPU time of the candidate plan is 2/3 of that of the latest plan.

    [1000, ∞) ms

    The CPU time of the candidate plan is 5/6 of that of the latest plan.

  • Take note of the following index recommendation rules:

    • The equality query columns are arranged in the front and combined in any order.

    • If range queries are performed, the range query column with the best selectivity is arranged after the equality query columns.

    • If no range query is performed, the sorting column is arranged after the equality query columns when the sorting elimination condition is met.

    • Other columns in the predicate are arranged after the index. In this case, the index is a half-width index.

    • Other columns in the SELECT statement are arranged after the index to construct a covering index.

    • If the covering index contains eight or fewer columns, the covering index is recommended. Otherwise, the half-width index is recommended.

    • An index is recommended only when the CPU time exceeds 10 ms or the response time exceeds 20 ms.

优化建议..png

SQL sampling details

Note

The sampling details of SQL statements are displayed only on the details page of slow SQL statements.

You can view the sampling details of the current SQL statement. You can click Column Management to specify the columns to be displayed and adjust their sequence. Click Export to download the list of SQL sampling details.

image

Aggregated SQL details

Note

The details of an aggregated SQL statement are displayed only on its details page.

On this page, you can view all related SQL statements. By default, the SQL text, database, and SQL ID columns are displayed. You can also view the error information about these SQL statements.

image.png

SQL diagnostic details

In the SQL Diagnostic Details section, you can view the number of occurrences of the same issue within the diagnostic time range, the request time range, number of executions, CPU time, and last execution time of the current SQL statement, and the diagnostics basis and troubleshooting suggestions for the current SQL statement.1

Note

This section is not displayed when no diagnostic details are available.

Physical execution plans

On the Physical execution plan tab, you can view, bind, and evict execution plans.

  • Click the plan hash text to view the details of the corresponding physical execution plan.

  • Click Custom Time and select Last Hour, Last 12 Hours, or Last Day from the drop-down list or specify a custom time range for filtering.

  • Click Binding History in Last 30 Days to view the binding history.

  • Click Evict Execution Plan. This operation clears the plan cache of the SQL statement in the tenant. An execution plan will be generated next time the SQL statement is executed.

  • Click Bind. This operation forces the SQL statement to be executed based on the execution plan. If the execution plan is bound to an SQL statement, Bound is displayed.

image

SQL execution history

On the SQL Execution History tab, the information about non-aggregated SQL statements and that about aggregated SQL statements are displayed in different layouts.

Execution history of non-aggregated SQL statements

You can view the execution history and statistical charts of an SQL statement in the last hour, last 12 hours, last day, or a custom time range.

  • Execution history: You can view the execution history of the current SQL statement. Click Column Management in the upper-right corner, select the information that needs to be frequently viewed, and click Export to download the list of execution history.

    image

  • Execution time: You can select a type of execution time on the right to view the statistics.

    image.png

  • Number of executions: You can click the Total Number of Executions drop-down list and select the data to view.

    image.png

Execution history of aggregated SQL statements

You can view the execution history and statistical charts of an SQL statement in the last hour, last 12 hours, last day, or a custom time range.

  • Execution history: You can view the execution history of the current SQL statement. Click Column Management in the upper-right corner, select the information that needs to be frequently viewed, and click Export to download the list of execution history.

    image.png

  • You can view the statistical charts of an aggregated SQL statement in the aggregated SQL dimension or single SQL dimension. In the aggregated SQL dimension, the SQL execution time -CPU time chart and the total execution charts are displayed by default.

    • The aggregated SQL dimension collects the overall data of associated SQL statements.

      Execution time: You can select Total Database Time, Database Time, or Response Time from the drop-down list to view the statistics.

      image.png

      Total executions: You can click the Total Number of Executions drop-down list and select the data to view.

      image.png

    • In the single SQL dimension, the top 10 SQL statements sorted by CPU utilization are displayed by default. You can select more SQL statements from the drop-down list.

      Execution time: You can select Total Database Time, Database Time, or Response Time from the drop-down list to view the statistics. You can also search for SQL statements by SQL ID and view the information about a specific SQL statement.

      image.png

      Total executions: You can click the Total Number of Executions drop-down list and select the data to view. You can also search for SQL statements by SQL ID and view the information about a specific SQL statement.

      image.png

You can select Executions per Second, Total Elapsed Time, or Elapsed Time from the Total Number of Executions drop-down list to view the statistics.

Table information

On the Table Information tab, you can view and bind indexes. If an index is bound to an SQL statement, Bound is displayed. You can also view table statistics on this tab.

image

Advanced settings

On the Advanced Settings tab, you can perform the following operations:

  1. Turn on the Throttling switch.

  2. Specify the maximum number of SQL statements that can be concurrently executed by an OBServer node. The index that is bound and the specified number of concurrently executed statements are mutually exclusive.

  3. Click Update Settings for your settings to take effect in real time.1