View execution plans of queries|V4.1.0| docs|Distributed Database

View execution plans of queries

Last Updated：2023-08-01 06:02:28 Updated

The SQL engine of OceanBase Database supports execution plan parsing and caching. You can analyze the query performance of a query statement by analyzing its execution plan.

An execution plan describes the execution procedure of an SQL statement in OceanBase Database. Execution plans are used to analyze the execution performance of SQL statements. Understanding execution plans is the precondition of SQL optimization. You can execute the EXPLAIN statement to view the logical execution plan generated by the optimizer for a given SQL statement.

For more information about SQL execution plans, see SQL execution plans.

For more information about distributed execution plans, see Manage distributed execution plans.

Format of the EXPLAIN statement

OceanBase Database supports three EXPLAIN statement formats: EXPLAIN BASIC, EXPLAIN, and EXPLAIN EXTENDED. They demonstrate details of execution plans at different levels:

The EXPLAIN BASIC statement shows the most basic framework of a plan.
The EXPLAIN EXTENDED statement extends a plan to its full frame with the most details and is usually used in troubleshooting.
The EXPLAIN statement shows information to the extent that helps users understand the entire execution process of a plan.

Syntax

EXPLAIN [explain_type] dml_statement;

explain_type:
    BASIC [pretty_name]
  | OUTLINE [pretty_name]
  | EXTENDED [pretty_name]
  | EXTENDED_NOADDR [pretty_name]
  | PARTITIONS [pretty_name]
  | FORMAT = { TRADITIONAL | JSON }

pretty_name:
  PRETTY
  | PRETTY_COLOR

dml_statement:
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement

The EXPLAIN statement applies to the SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. This statement shows information about statement execution plans provided by the optimizer, including the method of processing the statement and the method and sequence of joining tables.
You can execute the EXPLAIN EXTENDED statement to display the scan range of the table and execute the EXPLAIN OUTLINE statement to display the outline information.
The FORMAT option is used to specify the output format. The TRADITIONAL value is the default value and specifies to display information in a table. The JSON value specifies to display information in a JSON file.
You can use the EXPLAIN PARTITITIONS statement to check queries related to partitioned tables. If you check queries for non-partitioned tables, no error is returned, but the values in the PARTIONS column are always NULL.
For complex execution plans, you can use the PRETTY or PRETTY_COLOR option to connect the parent and child nodes in the plan tree with tree lines or colored tree lines, so that the execution plan is easier to read.

View an execution plan in ODC

In the SQL window of ODC, execute the EXPLAIN statement and click on the Result 1 tab to view the information about the execution plan.

Execute the EXPLAIN BASIC statement to display an execution plan.
Execute the EXPLAIN statement to display an execution plan.
Execute the EXPLAIN EXTENDED statement to display an execution plan.
Execute the EXPLAIN EXTENDED_NOADDR statement to display an execution plan.
Execute the EXPLAIN OUTLINE statement to display the outline information.

View an execution plan in CLI

Execute the EXPLAIN BASIC statement to display an execution plan.

obclient> CREATE TABLE t511(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t512(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected

obclient> EXPLAIN BASIC SELECT * FROM t511,t512 WHERE t511.a = t512.a\G
*************************** 1. row ***************************
Query Plan: ==========================
|ID|OPERATOR        |NAME|
--------------------------
|0 |NESTED-LOOP JOIN|    |
|1 | TABLE SCAN     |T511|
|2 | TABLE GET      |T512|
==========================

Outputs & filters:
-------------------------------------
  0 - output([T511.A], [T511.B], [T511.C], [T512.A], [T512.B], [T512.C]), filter(nil),
      conds(nil), nl_params_([T511.A])
  1 - output([T511.A], [T511.B], [T511.C]), filter(nil),
      access([T511.A], [T511.B], [T511.C]), partitions(p0)
  2 - output([T512.A], [T512.B], [T512.C]), filter(nil),
      access([T512.A], [T512.B], [T512.C]), partitions(p0)

1 row in set

Execute the EXPLAIN statement to display an execution plan.

obclient> EXPLAIN SELECT * FROM t511,t512 WHERE t511.a = t512.a\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST|
-----------------------------------------
|0 |NESTED-LOOP JOIN|    |1        |53  |
|1 | TABLE SCAN     |T511|1        |46  |
|2 | TABLE GET      |T512|1        |7   |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([T511.A], [T511.B], [T511.C], [T512.A], [T512.B], [T512.C]), filter(nil),
      conds(nil), nl_params_([T511.A])
  1 - output([T511.A], [T511.B], [T511.C]), filter(nil),
      access([T511.A], [T511.B], [T511.C]), partitions(p0)
  2 - output([T512.A], [T512.B], [T512.C]), filter(nil),
      access([T512.A], [T512.B], [T512.C]), partitions(p0)

1 row in set

Execute the EXPLAIN EXTENDED statement to display an execution plan.

obclient> EXPLAIN EXTENDED SELECT * FROM t511,t512 WHERE t511.a = t512.a\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST|
-----------------------------------------
|0 |NESTED-LOOP JOIN|    |1        |53  |
|1 | TABLE SCAN     |T511|1        |46  |
|2 | TABLE GET      |T512|1        |7   |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([T511.A(0x7f03f423b040)], [T511.B(0x7f03f423b840)], [T511.C(0x7f03f423bb00)], [T512.A(0x7f03f423b300)], [T512.B(0x7f03f423bdc0)], [T512.C(0x7f03f423c080)]), filter(nil),
      conds(nil), nl_params_([T511.A(0x7f03f423b040)]), batch_join=false
  1 - output([T511.A(0x7f03f423b040)], [T511.B(0x7f03f423b840)], [T511.C(0x7f03f423bb00)]), filter(nil),
      access([T511.A(0x7f03f423b040)], [T511.B(0x7f03f423b840)], [T511.C(0x7f03f423bb00)]), partitions(p0),
      is_index_back=false,
      range_key([T511.A(0x7f03f423b040)]), range(MIN ; MAX)always true
  2 - output([T512.A(0x7f03f423b300)], [T512.B(0x7f03f423bdc0)], [T512.C(0x7f03f423c080)]), filter(nil),
      access([T512.A(0x7f03f423b300)], [T512.B(0x7f03f423bdc0)], [T512.C(0x7f03f423c080)]), partitions(p0),
      is_index_back=false,
      range_key([T512.A(0x7f03f423b300)]), range(MIN ; MAX)
      range_cond([? = T512.A(0x7f03f423b300)(0x7f03f429aee0)])

Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$1" ("SYS.T511"@"SEL$1" "SYS.T512"@"SEL$1"))
      USE_NL(@"SEL$1" "SYS.T512"@"SEL$1")
      FULL(@"SEL$1" "SYS.T511"@"SEL$1")
      FULL(@"SEL$1" "SYS.T512"@"SEL$1")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------


T511:table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=cost_based, avaiable_index_name[T511]

T512:table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback

Parameters
-------------------------------------

1 row in set

Execute the EXPLAIN EXTENDED_NOADDR statement to display an execution plan.

obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t511,t512 WHERE t511.a = t512.a\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST|
-----------------------------------------
|0 |NESTED-LOOP JOIN|    |1        |53  |
|1 | TABLE SCAN     |T511|1        |46  |
|2 | TABLE GET      |T512|1        |7   |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([T511.A], [T511.B], [T511.C], [T512.A], [T512.B], [T512.C]), filter(nil),
      conds(nil), nl_params_([T511.A]), batch_join=false
  1 - output([T511.A], [T511.B], [T511.C]), filter(nil),
      access([T511.A], [T511.B], [T511.C]), partitions(p0),
      is_index_back=false,
      range_key([T511.A]), range(MIN ; MAX)always true
  2 - output([T512.A], [T512.B], [T512.C]), filter(nil),
      access([T512.A], [T512.B], [T512.C]), partitions(p0),
      is_index_back=false,
      range_key([T512.A]), range(MIN ; MAX),
      range_cond([? = T512.A])

1 row in set

Execute the EXPLAIN OUTLINE statement to display the outline information.

obclient> EXPLAIN OUTLINE SELECT * FROM t511,t512 WHERE t511.a = t512.a\G
*************************** 1. row ***************************
Query Plan: =========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST|
-----------------------------------------
|0 |NESTED-LOOP JOIN|    |1        |53  |
|1 | TABLE SCAN     |T511|1        |46  |
|2 | TABLE GET      |T512|1        |7   |
=========================================

Outputs & filters:
-------------------------------------
  0 - output([T511.A], [T511.B], [T511.C], [T512.A], [T512.B], [T512.C]), filter(nil),
      conds(nil), nl_params_([T511.A])
  1 - output([T511.A], [T511.B], [T511.C]), filter(nil),
      access([T511.A], [T511.B], [T511.C]), partitions(p0)
  2 - output([T512.A], [T512.B], [T512.C]), filter(nil),
      access([T512.A], [T512.B], [T512.C]), partitions(p0)

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$1" ("SYS.T511"@"SEL$1" "SYS.T512"@"SEL$1"))
      USE_NL(@"SEL$1" "SYS.T512"@"SEL$1")
      FULL(@"SEL$1" "SYS.T511"@"SEL$1")
      FULL(@"SEL$1" "SYS.T512"@"SEL$1")
      END_OUTLINE_DATA
  */

1 row in set