Purpose
This statement is used to explain the execution plan of an SQL statement, which can be a SELECT, DELETE, INSERT, REPLACE, or UPDATE statement.
EXPLAIN is synonymous with DESCRIBE and DESC.
Syntax
{EXPLAIN [INTO table_name ] [SET statement_id = string]
| DESCRIBE
| DESC}
[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
| REPLACE statement
Parameters
| Parameter | Description |
|---|---|
| INTO table_name | Specifies to save the EXPLAIN plan information into the specified table. If INTO table_name is not specified, the default is to query the PLAN_TABLE table. |
| SET statement_id | Specifies a string tag for the current query to facilitate subsequent queries for the plan information of this SQL statement. If SET statement_id is not specified, an empty string is used as the information tag by default. |
| PRETTY | PRETTY_COLOR | Connects the parent and child nodes of the plan tree with tree lines or colored tree lines, making the execution plan easier to read. |
| BASIC | Specifies to output the basic information of the plan, such as operator ID, operator name, and referenced table name. |
| OUTLINE | Specifies to output the plan information, including OUTLINE information. |
| EXTENDED | EXPLAIN generates additional information, including: input and output columns of each operator, partition information of the accessed table, current Filter information, and if the current operator uses an index, the index columns and query range extracted. |
| EXTENDED_NOADDR | Displays the additional information in a simplified manner. |
| PARTITIONS | Displays partition-related information. |
| FORMAT = {TRADITIONAL| JSON} | Specifies the output format of EXPALIN:
|
| dml_statement | DML statement. |
Examples
Explain the query plan and save the plan information to the specified table
test./* Create the test table. */ obclient> CREATE TABLE test (statement_id VARCHAR(30), plan_id INT, gmt_create TIMESTAMP, remarks VARCHAR(4000), operator VARCHAR(255), options VARCHAR(255), object_node VARCHAR(40), object_owner VARCHAR(128), object_name VARCHAR(128), object_alias VARCHAR(261), object_instance INT, object_type VARCHAR(30),optimizer VARCHAR(4000), search_columns INT, id INT, parent_id INT, depth INT, position INT,is_last_child INT, cost INT, cardinality INT, bytes INT, rowset INT, other_tag VARCHAR(4000), partition_start VARCHAR(4000), partition_stop VARCHAR(4000), partition_id INT, other VARCHAR(4000), distribution VARCHAR(64), cpu_cost INT, io_cost INT, temp_space INT, access_predicates VARCHAR(4000), filter_predicates VARCHAR(4000),startup_predicates VARCHAR(4000), projection VARCHAR(4000), special_predicates VARCHAR(4000), time INT, qblock_name VARCHAR(128), other_xml VARCHAR(4000)); Query OK, 0 rows affected obclient> CREATE TABLE t1(c1 INT); Query OK, 0 rows affected /* Explain the query plan and save the plan information to the specified table test. */ obclient> EXPLAIN INTO test SELECT COUNT(*) FROM t1; +--------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |2 | | | |1 |└─TABLE FULL SCAN|t1 |1 |2 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=256 | | group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]) | | 1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 | | access(nil), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +--------------------------------------------------------------------------+ 14 rows in set obclient> SELECT DBMS_XPLAN.DISPLAY('TYPICAL',null,'test')\G *************************** 1. row *************************** DBMS_XPLAN.DISPLAY('TYPICAL',null,'test'): ================================================= |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| ------------------------------------------------- |0 |SCALAR GROUP BY | |1 |2 | |1 |└─TABLE FULL SCAN|t1 |1 |2 | ================================================= Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=256 group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]) 1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 access(nil), partitions(p0) is_index_back=false, is_global_index=false, range_key([t1.__pk_increment]), range(MIN ; MAX)always true 1 row in setExplain the query plan and set the query identifier.
obclient> EXPLAIN INTO test SET statement_id='test2' SELECT COUNT(*) FROM t1; +--------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |2 | | | |1 |└─TABLE FULL SCAN|t1 |1 |2 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=256 | | group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]) | | 1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 | | access(nil), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +--------------------------------------------------------------------------+ 14 rows in set obclient> SELECT DBMS_XPLAN.DISPLAY('TYPICAL','test2','test')\G *************************** 1. row *************************** DBMS_XPLAN.DISPLAY('TYPICAL','test2','test'): ================================================= |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| ------------------------------------------------- |0 |SCALAR GROUP BY | |1 |2 | |1 |└─TABLE FULL SCAN|t1 |1 |2 | ================================================= Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=256 group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]) 1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=256 access(nil), partitions(p0) is_index_back=false, is_global_index=false, range_key([t1.__pk_increment]), range(MIN ; MAX)always true 1 row in setOmit
explain_typeand return the execution plan of the statement.obclient> EXPLAIN SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4; +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH JOIN | |1 |9 | | | |1 |├─TABLE FULL SCAN|t2 |1 |4 | | | |2 |└─TABLE FULL SCAN|t1 |2 |4 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), rowset=16 | | equal_conds([t1.c2 = t2.c2]), other_conds(nil) | | 1 - output([t2.c2], [t2.c1]), filter([t2.c1 > 4]), rowset=16 | | access([t2.c2], [t2.c1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t2.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([t1.c2], [t1.c1]), filter(nil), rowset=16 | | access([t1.c2], [t1.c1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+Use the
EXTENDED_NOADDRkeyword to output additional information.obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4; +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH JOIN | |1 |9 | | | |1 |├─TABLE FULL SCAN|t2 |1 |4 | | | |2 |└─TABLE FULL SCAN|t1 |2 |4 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), rowset=16 | | equal_conds([t1.c2 = t2.c2]), other_conds(nil) | | 1 - output([t2.c2], [t2.c1]), filter([t2.c1 > 4]), rowset=16 | | access([t2.c2], [t2.c1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t2.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([t1.c2], [t1.c1]), filter(nil), rowset=16 | | access([t1.c2], [t1.c1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | | Used Hint: | | ------------------------------------- | | /*+ | | | | */ | | Qb name trace: | | ------------------------------------- | | stmt_id:0, stmt_type:T_EXPLAIN | | stmt_id:1, SEL$1 | | Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | LEADING(@"SEL$1" ("mysql"."t2"@"SEL$1" "mysql"."t1"@"SEL$1")) | | USE_HASH(@"SEL$1" "mysql"."t1"@"SEL$1") | | FULL(@"SEL$1" "mysql"."t2"@"SEL$1") | | FULL(@"SEL$1" "mysql"."t1"@"SEL$1") | | OPTIMIZER_FEATURES_ENABLE('4.0.0.0') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | t2: | | table_rows:3 | | physical_range_rows:3 | | logical_range_rows:3 | | index_back_rows:0 | | output_rows:0 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[t2] | | stats version:0 | | dynamic sampling level:1 | | t1: | | table_rows:2 | | physical_range_rows:2 | | logical_range_rows:2 | | index_back_rows:0 | | output_rows:2 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[t1] | | stats version:0 | | dynamic sampling level:1 | | Plan Type: | | LOCAL | | Note: | | Degree of Parallelisim is 1 because of table property | +------------------------------------------------------------------------------------+Display the execution plan in
TRADITIONALformat.obclient> EXPLAIN FORMAT=TRADITIONAL SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4; +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH JOIN | |1 |9 | | | |1 |├─TABLE FULL SCAN|t2 |1 |4 | | | |2 |└─TABLE FULL SCAN|t1 |2 |4 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), rowset=16 | | equal_conds([t1.c2 = t2.c2]), other_conds(nil) | | 1 - output([t2.c2], [t2.c1]), filter([t2.c1 > 4]), rowset=16 | | access([t2.c2], [t2.c1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([t2.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([t1.c2], [t1.c1]), filter(nil), rowset=16 | | access([t1.c2], [t1.c1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+Display the execution plan in
JSONformat.obclient> EXPLAIN FORMAT=JSON SELECT * FROM t1,t2 WHERE t1.c2=t2.c2 AND t2.c1 > 4; +----------------------------------------------------------+ | Query Plan | +----------------------------------------------------------+ | { | | "ID":0, | | "OPERATOR":"HASH JOIN ", | | "NAME":"", | | "EST.ROWS":1, | | "EST.TIME(us)":9, | | "output":"output([t1.c1], [t1.c2], [t2.c1], [t2.c2])", | | "CHILD_1": { | | "ID":1, | | "OPERATOR":"TABLE FULL SCAN", | | "NAME":"t2", | | "EST.ROWS":1, | | "EST.TIME(us)":4, | | "output":"output([t2.c2], [t2.c1])" | | }, | | "CHILD_2": { | | "ID":2, | | "OPERATOR":"TABLE FULL SCAN", | | "NAME":"t1", | | "EST.ROWS":2, | | "EST.TIME(us)":4, | | "output":"output([t1.c2], [t1.c1])" | | } | | } | +----------------------------------------------------------+