View execution plans of queries

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 run the EXPLAIN command to view the logical execution plan generated by the optimizer for a given SQL statement.

Syntax of the EXPLAIN command

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

• The EXPLAIN BASIC command shows the most basic framework of a plan.

• The EXPLAIN EXTENDED command extends a plan to its full frame with the most details and is usually used in troubleshooting.

• The EXPLAIN command 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 command applies to the SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. This command 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 run the EXPLAIN EXTENDED command to display the scan range of the table and run the EXPLAIN OUTLINE command 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.

• Run the EXPLAIN BASIC command to display an execution plan.

  

• Run the EXPLAIN command to display an execution plan.

  

• Run the EXPLAIN EXTENDED command to display an execution plan.

  

• Run the EXPLAIN EXTENDED_NOADDR command to display an execution plan.

  

• Run the EXPLAIN OUTLINE command to display the outline information.

  

View an execution plan in CLI

• Run the EXPLAIN BASIC command 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
  

• Run the EXPLAIN command 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
  

• Run the EXPLAIN EXTENDED command 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
  

• Run the EXPLAIN EXTENDED_NOADDR command 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
  

• Run the EXPLAIN OUTLINE command 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