An execution plan describes the process of executing an SQL query statement in OceanBase Database. You can execute the EXPLAIN statement to view the logical execution plan generated by the optimizer for a given SQL statement.
To analyze the performance of an SQL statement, you need to first check the SQL execution plan to see if any error exists. Therefore, understanding the execution plan is the first step for SQL tuning, and knowledge about operators of an execution plan is key to understanding the EXPLAIN statement.
Syntax of the EXPLAIN statement
OceanBase Database supports three EXPLAIN modes: EXPLAIN BASIC, EXPLAIN, and EXPLAIN EXTENDED. They demonstrate details of execution plans at different levels:
The
EXPLAIN BASICstatement shows the most basic framework of a plan.The
EXPLAIN EXTENDEDstatement extends a plan to its full frame with the most details and is usually used in troubleshooting.The
EXPLAINstatement shows information to the extent that helps users understand the entire execution process of a plan.
The syntax of the EXPLAIN statement is as follows:
EXPLAIN [INTO table_name ] [SET statement_id = string] [explain_type] [PRETTY | PRETTY_COLOR] dml_statement;
explain_type:
BASIC
| OUTLINE
| EXTENDED
| EXTENDED_NOADDR
| PARTITIONS
| FORMAT = {TRADITIONAL| JSON}
dml_statement:
SELECT statement
| DELETE statement
| INSERT statement
| UPDATE statement
/* Supported only in Oracle-compatible mode */
| MERGE statement
/* Supported only in MySQL-compatible mode */
| REPLACE statement
The EXPLAIN statement applies to the SELECT, DELETE, INSERT, REPLACE, and UPDATE statements. It 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 use the EXPLAIN EXTENDED statement to display the scan range of the table and use 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 PARTITIONS 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 PARTITIONS column are always NULL.
You can use INTO table_name to save the execution plan obtained by EXPLAIN to a specified table. By default, the plan is saved to the PLAN_TABLE table. You can use SET statement_id to set an ID for the current query, so that the execution plan of the SQL query can be obtained based on the ID when necessary. By default, an empty string is used as the ID. For more information, see EXPLAIN.
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. Here is an example:
obclient> CREATE TABLE p1table(c1 INT ,c2 INT) PARTITION BY HASH(c1) PARTITIONS 2;
Query OK, 0 rows affected
obclient> CREATE TABLE p2table(c1 INT ,c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> EXPLAIN EXTENDED PRETTY_COLOR SELECT * FROM p1table p1 JOIN p2table p2 ON p1.c1=p2.c2;
*************************** 1. row ***************************
Query Plan: ==========================================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------------------------
|0 |PX COORDINATOR | |1 |278 |
|1 | EXCHANGE OUT DISTR |:EX10001|1 |277 |
|2 | HASH JOIN | |1 |276 |
|3 | ├PX PARTITION ITERATOR | |1 |92 |
|4 | │ TABLE SCAN |P1 |1 |92 |
|5 | └EXCHANGE IN DISTR | |1 |184 |
|6 | EXCHANGE OUT DISTR (PKEY)|:EX10000|1 |184 |
|7 | PX PARTITION ITERATOR | |1 |183 |
|8 | TABLE SCAN |P2 |1 |183 |
==========================================================
Outputs & filters:
-------------------------------------
0 - output([INTERNAL_FUNCTION(P1.C1, P1.C2, P2.C1, P2.C2)]), filter(nil)
1 - output([INTERNAL_FUNCTION(P1.C1, P1.C2, P2.C1, P2.C2)]), filter(nil), dop=1
2 - output([P1.C1], [P2.C2], [P1.C2], [P2.C1]), filter(nil),
equal_conds([P1.C1 = P2.C2]), other_conds(nil)
3 - output([P1.C1], [P1.C2]), filter(nil)
4 - output([P1.C1], [P1.C2]), filter(nil),
access([P1.C1], [P1.C2]), partitions(p[0-1])
5 - output([P2.C2], [P2.C1]), filter(nil)
6 - (#keys=1, [P2.C2]), output([P2.C2], [P2.C1]), filter(nil), dop=1
7 - output([P2.C1], [P2.C2]), filter(nil)
8 - output([P2.C1], [P2.C2]), filter(nil),
access([P2.C1], [P2.C2]), partitions(p[0-3])
1 row in set
Shape and operators of a plan
In a database system, an execution plan is usually represented in a tree-like structure. However, different databases display execution plans in different ways.
The following examples show the execution plans for TPC-DS Q3 in PostgreSQL, Oracle, and OceanBase Database.
obclient> SELECT /*TPC-DS Q3*/ *
FROM (SELECT dt.d_year,
item.i_brand_id brand_id,
item.i_brand brand,
Sum(ss_net_profit) sum_agg
FROM date_dim dt,
store_sales,
item
WHERE dt.d_date_sk = store_sales.ss_sold_date_sk
AND store_sales.ss_item_sk = item.i_item_sk
AND item.i_manufact_id = 914
AND dt.d_moy = 11
GROUP BY dt.d_year,
item.i_brand,
item.i_brand_id
ORDER BY dt.d_year,
sum_agg DESC,
brand_id)
WHERE ROWNUM <= 100;
The execution plan in PostgreSQL is as follows:
Limit (cost=13986.86..13987.20 rows=27 width=91) Sort (cost=13986.86..13986.93 rows=27 width=65) Sort Key: dt.d_year, (sum(store_sales.ss_net_profit)), item.i_brand_id HashAggregate (cost=13985.95..13986.22 rows=27 width=65) Merge Join (cost=13884.21..13983.91 rows=204 width=65) Merge Cond: (dt.d_date_sk = store_sales.ss_sold_date_sk) Index Scan using date_dim_pkey on date_dim dt (cost=0.00..3494.62 rows=6080 width=8) Filter: (d_moy = 11) Sort (cost=12170.87..12177.27 rows=2560 width=65) Sort Key: store_sales.ss_sold_date_sk Nested Loop (cost=6.02..12025.94 rows=2560 width=65) Seq Scan on item (cost=0.00..1455.00 rows=16 width=59) Filter: (i_manufact_id = 914) Bitmap Heap Scan on store_sales (cost=6.02..658.94 rows=174 width=14) Recheck Cond: (ss_item_sk = item.i_item_sk) Bitmap Index Scan on store_sales_pkey (cost=0.00..5.97 rows=174 width=0) Index Cond: (ss_item_sk = item.i_item_sk)The execution plan in Oracle Database is as follows:
Plan hash value: 2331821367 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 9100 | 3688 (1)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 2736 | 243K| 3688 (1)| 00:00:01 | |* 3 | SORT ORDER BY STOPKEY | | 2736 | 256K| 3688 (1)| 00:00:01 | | 4 | HASH GROUP BY | | 2736 | 256K| 3688 (1)| 00:00:01 | |* 5 | HASH JOIN | | 2736 | 256K| 3686 (1)| 00:00:01 | |* 6 | TABLE ACCESS FULL | DATE_DIM | 6087 | 79131 | 376 (1)| 00:00:01 | | 7 | NESTED LOOPS | | 2865 | 232K| 3310 (1)| 00:00:01 | | 8 | NESTED LOOPS | | 2865 | 232K| 3310 (1)| 00:00:01 | |* 9 | TABLE ACCESS FULL | ITEM | 18 | 1188 | 375 (0)| 00:00:01 | |* 10 | INDEX RANGE SCAN | SYS_C0010069 | 159 | | 2 (0)| 00:00:01 | | 11 | TABLE ACCESS BY INDEX ROWID| STORE_SALES | 159 | 2703 | 163 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------The execution plan in OceanBase Database is as follows:
|ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------- |0 |LIMIT | |100 |81141| |1 | TOP-N SORT | |100 |81127| |2 | HASH GROUP BY | |2924 |68551| |3 | HASH JOIN | |2924 |65004| |4 | SUBPLAN SCAN |VIEW1 |2953 |19070| |5 | HASH GROUP BY | |2953 |18662| |6 | NESTED-LOOP JOIN| |2953 |15080| |7 | TABLE SCAN |ITEM |19 |11841| |8 | TABLE SCAN |STORE_SALES|161 |73 | |9 | TABLE SCAN |DT |6088 |29401| =======================================================
You may notice from the foregoing examples that a plan in OceanBase Database is similar to that in Oracle Database.
The following table describes columns of an execution plan in OceanBase Database.
Column name |
Description |
|---|---|
ID |
The sequence number of the execution tree obtained by using preorder traversal, starting from 0. |
OPERATOR |
The name of the operator. |
NAME |
The name of the table or index corresponding to a table operation. |
EST. ROWS |
The estimated number of output rows of the operator. |
COST |
The execution cost of the operator, in microseconds. |
Note
In a table operation, the NAME field displays names (aliases) of tables involved in the operation. In the case of index access, the name of the index is displayed in parentheses after the table name. For example, t1(t1_c2) indicates that index t1_c2 is used. In the case of reverse scanning, the keyword REVERSE is added after the index name, with the index name and the keyword REVERSE separated with a comma (,), for example, t1(t1_c2,REVERSE).
In OceanBase Database, the first part of the output of EXPLAIN is the tree structure of the execution plan. The hierarchy of operations in the tree is represented by their indentations in the operator column. Operators at the deepest level are executed first. Operators at the same level are executed in the specified execution order.
The following figure shows the execution plan tree for TPC-DS Q3.
In OceanBase Database, the second part of the output of EXPLAIN contains the details of each operator, including the output expression, filter conditions, partition information, and unique information of each operator, such as the sort keys, join keys, and pushdown conditions. Here is an example:
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC]), prefix_pos(1)
1 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
equal_conds([t1.c1 = t2.c2]), other_conds(nil)
2 - output([t2.c1], [t2.c2]), filter(nil), sort_keys([t2.c2, ASC])
3 - output([t2.c2], [t2.c1]), filter(nil),
access([t2.c2], [t2.c1]), partitions(p0)
4 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0)
For better understanding of plans obtained by EXPLAIN in OceanBase Database, see Execution plan operators.