All Products
Search
Document Center

AnalyticDB for MySQL:Use execution plans to analyze queries

Last Updated:Mar 01, 2024

AnalyticDB for MySQL provides the SQL diagnostics feature to display execution plans of SQL queries in hierarchy charts. The hierarchy chart for an execution plan consists of two layers. The first layer is the stage layer, and the second layer is the operator layer. This topic describes how to use the stage and operator layers for execution plan hierarchy charts to analyze queries.

Execution plan hierarchy chart at the stage layer

An execution plan hierarchy chart consists of multiple stages at the stage layer, where data flows from the bottom up. First, the stage that contains scan operators scans data. Then, the data is processed by multiple intermediate stage nodes. Finally, the root node at the top returns the query results to the client.

1
The execution plan hierarchy chart at the stage layer contains the following information:
  • Basic information
    Each rectangle in the preceding figure represents a stage and contains information of the stage, including the stage ID, data output type, and duration or consumed memory. Memory information is displayed after you select By Memory.
    Note If the system identifies possible optimization operations for a stage, a red icon that contains an exclamation point (!) is displayed for that stage of the hierarchy chart.
  • Number of output rows

    The number on the line between two adjacent stages indicates the number of output rows from an upstream stage to a downstream stage. The larger the number of output rows, the thicker the line between stages.

  • Data output method
    This method is used to transfer data between two adjacent stages from the upstream to the downstream. The following table describes the data output methods supported by AnalyticDB for MySQL.
    Data output methodDescription
    BroadcastThe data of each compute node in an upstream stage is copied to all compute nodes of a downstream stage. 1
    RepartitionThe data of each compute node in an upstream stage is partitioned based on specific rules and then distributed to the specified compute nodes of a downstream stage.2
    GatherThe data of each compute node in an upstream stage is concentrated on a specific compute node in a downstream stage. 3
  • View the details of the stages that rank top 10 in terms of memory usage or execution duration
    The right-side Top 10 Nodes in Descending Order by Duration or Memory tab displays the IDs and corresponding proportions of the top 10 stages. The top 10 stages have the largest proportion of the execution duration to the total query duration or the largest proportion of the used memory to the total query memory.
    Note
    • By default, By Duration is selected. You can also select By Memory in the upper-right corner of the execution plan hierarchy chart.
    • The stages whose memory usage is less than 1% or the stages that have an execution duration proportion of less than 1% are not displayed on the Top 10 Nodes in Descending Order by Duration or Memory tab.
    • The sum of the execution duration proportions or memory usage of all stages in a query may not be 100% due to differences in statistical methods.
  • Diagnostic Results
    Click a stage such as Stage[1] in the execution plan hierarchy chart to view the following diagnostics details of the stage in the right-side Diagnostic Results section:
    • Stage Diagnostics: provides a detailed description of the stage diagnostic results, including the diagnosed issues and the corresponding optimization solutions. These issues may be large amounts of broadcast data or data skew.
    • Operator Diagnostics: provides an overview of faulty operators in the current stage and their corresponding issues. The detailed descriptions and optimization solutions are available only in the execution plan hierarchy chart at the operator layer. For more information, see Execution plan hierarchy chart at the operator layer.

    For more information about stage diagnostic results, see Stage-level diagnostic results.

  • Statistics

    The Statistics section below the Diagnostic Results section shows the metric statistics for the stage that you want to view.

    MetricDescription
    Peak MemoryThe maximum memory consumed by the stage. The system selects Bytes, KB, MB, GB, or TB as the unit based on the actual amount of memory consumed.
    Cumulative DurationThe cumulative execution duration consumed by all nodes and threads of all operators in the stage memory. The system selects ms, s, m, or h as the unit based on the actual duration.
    Note This cumulative duration cannot be compared with the total duration of the current query.
    Output RowsThe number of rows output from the stage.
    Amount of Output DataThe amount of data output from the stage. The system selects Bytes, KB, MB, GB, or TB as the unit based on the actual amount of data.
    Input RowsThe number of rows input to the stage.
    Amount of Input DataThe amount of data input to the stage. The system selects Bytes, KB, MB, GB, or TB as the unit based on the actual amount of data.
    Scanned RowsThe number of rows scanned by the stage.
    Note This parameter is displayed only when the stage contains scan operators.
    Scan SizeThe amount of data scanned by the stage. The system selects Bytes, KB, MB, GB, or TB as the unit based on the actual amount of data.
    Note This parameter is displayed only when the stage contains scan operators.

Execution plan hierarchy chart at the operator layer

An execution plan hierarchy chart consists of multiple operators at the operator layer, where data flows from the bottom up. First, the most upstream operators (TableScan and RemoteSource) scan data or receive network data. Then, the data is processed by multiple intermediate operator nodes. Finally, the root node (StageOutput or Output) at the top returns the query results to the client.

You can move the pointer over the stage that you want to view and click View Stage Plans in the information box that appears. Then, you can go to the plan details page of the stage and view the execution plan hierarchy chart at the operator layer. 3
The execution plan hierarchy chart at the operator layer contains the following information.4
Note If the system identifies possible optimization operations for an operator, a red icon that contains an exclamation point (!) is displayed for that operator of the hierarchy chart.
  • Basic information

    Each rectangle in the preceding figure represents an operator and contains information of the operator, including the operator name, ID, properties (such as the join conditions and algorithms of the JOIN operator), and duration or consumed memory. Memory information is displayed after you select By Memory.

  • Number of output rows

    The number on the line between two adjacent operators indicates the number of rows output from an upstream operator to a downstream operator. The larger the number of output rows, the thicker the line between operators.

  • View the details of the operators that rank top 10 in terms of memory usage or execution duration
    The right-side Top 10 Nodes in Descending Order by Duration or Memory tab displays the IDs and corresponding proportions of the top 10 operators. The top 10 operators have the largest proportion of the execution duration to the total query duration or the largest proportion of the used memory to the total query memory.
    Note
    • By default, By Duration is selected. You can also select By Memory in the upper-right corner of the execution plan hierarchy chart.
    • The operators whose memory usage is less than 1% or the operators that have an execution duration proportion of less than 1% are not displayed on the Top 10 Nodes in Descending Order by Duration or Memory tab.
    • The sum of the execution duration proportions or memory usage of all operators in a stage may not be 100% due to differences in statistical methods.
  • Diagnostic Results
    Click an operator such as Join[36184] in the execution plan hierarchy chart to view the diagnostics details of the operator in the right-side Diagnostic Results section. The diagnostics details include diagnosed issues and the corresponding optimization solutions. These issues may be data skew or large right tables in joins. For more information, see Operator-level diagnosis results. 5
  • Statistics

    The Statistics section below the Diagnostic Results section shows the metric statistics for the operator that you want to view.

    MetricDescription
    Peak MemoryThe maximum memory consumed by the operator. The system selects Bytes, KB, MB, GB, or TB as the unit based on the actual amount of memory consumed.
    Time ConsumedThe average duration of the operator with a specific concurrency. The system selects ms, s, m, or h as the unit based on the actual duration.
    Note This duration can be compared with the duration of the current query.
    Output RowsThe number of rows output from the operator.
    Amount of Output DataThe amount of data output from the operator. The system selects Bytes, KB, MB, GB, or TB as the unit based on the actual amount of data.
    Input RowsThe number of rows input to the operator.
    Amount of Input DataThe amount of data input to the operator. The system selects Bytes, KB, MB, GB, or TB as the unit based on the actual amount of data.
    Builder StatisticsThe statistics for the builder, including the builder type, maximum memory, duration, number of input rows, number of output rows, and data amount. The following builder types are available:
    • HashBuilder: builds hash tables to complete hash joins.
    • SetBuilder: builds sets to complete semi joins.
    • NestLoopBuilder: completes nested-loop joins (NLJs).
    Note This metric is displayed only for the JOIN operator.
    PropertiesThe properties of the operator. Different operators have different properties. For example, the properties of the JOIN operator include the join type and the join method. For more information, see Operators.