Purpose
You can use this statement to obtain the execution plan of an SQL statement, which can be a SELECT, DELETE, INSERT, REPLACE or UPDATE statement.
EXPLAIN, DESCRIBE, and DESC are synonyms.
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 | The table where the execution plan obtained by EXPLAIN is to be stored. If you do not specify INTO table_name, the execution plan is stored in the PLAN_TABLE table by default. |
| SET statement_id | The statement ID of the explained SQL statement, which can be used to query the execution plan of the statement later. If you do not specify SET statement_id, an empty string is used as the statement ID by default. |
| PRETTY | PRETTY_COLOR | Specifies to connect the parent and child nodes in the plan tree with tree lines or colored tree lines to make the execution plan easier to read. |
| BASIC | The basic information about the output plan, such as the operator ID, operator name, and name of the referenced table. |
| OUTLINE | The outline information contained in the output plan. |
| EXTENDED | The extra information generated by EXPLAIN, including the input and output columns of each operator, partition information about the access table, and the active filter. If the current operator uses an index, the used index columns and extracted query range are displayed. |
| EXTENDED_NOADDR | Specifies to display the brief extra information. |
| PARTITIONS | Specifies to display the partition-related information. |
| FORMAT = {TRADITIONAL| JSON} | The output format of EXPLAIN. Valid values:
|
| dml_statement | The DML statement. |
Examples
Explain an SQL query and store the obtained execution plan in the
testtable./* Create a table named test. */ 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, c2 INT); Query OK, 0 rows affected obclient [test]> CREATE TABLE t2(c1 INT, c2 INT); Query OK, 0 rows affected /* Explain an SQL query and store the obtained execution plan in the test table. */ obclient> EXPLAIN INTO test SELECT COUNT(*) FROM t1; +-------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |SCALAR GROUP BY | |1 |3 | | | |1 |└─TABLE FULL SCAN|t1 |1 |3 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]) | | 1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=16 | | access(nil), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true, | | pushdown_aggregation([T_FUN_COUNT(*)]) | +-------------------------------------------------------------------------+ 15 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 |3 | |1 |└─TABLE FULL SCAN|t1 |1 |3 | ================================================= Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=16 group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]) 1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=16 access(nil), partitions(p0) is_index_back=false, is_global_index=false, range_key([t1.__pk_increment]), range(MIN ; MAX)always true, pushdown_aggregation([T_FUN_COUNT(*)]) 1 row in setExplain an SQL query and set an ID for the query.
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 |3 | | | |1 |└─TABLE FULL SCAN|t1 |1 |3 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=16 | | group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]) | | 1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=16 | | access(nil), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([t1.__pk_increment]), range(MIN ; MAX)always true, | | pushdown_aggregation([T_FUN_COUNT(*)]) | +-------------------------------------------------------------------------+ 15 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 |3 | |1 |└─TABLE FULL SCAN|t1 |1 |3 | ================================================= Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), rowset=16 group(nil), agg_func([T_FUN_COUNT_SUM(T_FUN_COUNT(*))]) 1 - output([T_FUN_COUNT(*)]), filter(nil), rowset=16 access(nil), partitions(p0) is_index_back=false, is_global_index=false, range_key([t1.__pk_increment]), range(MIN ; MAX)always true, pushdown_aggregation([T_FUN_COUNT(*)]) 1 row in setExplain an SQL query to obtain its execution plan without specifying
explain_type.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 |5 | | | |1 |├─TABLE FULL SCAN|t1 |1 |3 | | | |2 |└─TABLE FULL SCAN|t2 |1 |3 | | | ================================================= | | 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([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 | | 2 - 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 | +------------------------------------------------------------------------------------+ 19 rows in setUse the
EXTENDED_NOADDRkeyword to output additional information in the execution plan.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 |5 | | | |1 |├─TABLE FULL SCAN|t1 |1 |3 | | | |2 |└─TABLE FULL SCAN|t2 |1 |3 | | | ================================================= | | 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([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 | | 2 - 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 | | 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" ("test"."t1"@"SEL$1" "test"."t2"@"SEL$1")) | | USE_HASH(@"SEL$1" "test"."t2"@"SEL$1") | | FULL(@"SEL$1" "test"."t1"@"SEL$1") | | FULL(@"SEL$1" "test"."t2"@"SEL$1") | | OPTIMIZER_FEATURES_ENABLE('4.3.0.0') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | t1: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[t1] | | stats version:0 | | dynamic sampling level:1 | | estimation method:[DYNAMIC SAMPLING FULL] | | t2: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:1 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[t2] | | stats version:0 | | dynamic sampling level:1 | | estimation method:[DYNAMIC SAMPLING FULL] | | Plan Type: | | LOCAL | | Note: | | Degree of Parallelisim is 1 because of table property | +------------------------------------------------------------------------------------+ 69 rows in setShow an execution plan in the
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 |5 | |
| |1 |├─TABLE FULL SCAN|t1 |1 |3 | |
| |2 |└─TABLE FULL SCAN|t2 |1 |3 | |
| ================================================= |
| 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([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 |
| 2 - 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 |
+------------------------------------------------------------------------------------+
19 rows in set
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)":5, | | "output":"output([t1.c1], [t1.c2], [t2.c1], [t2.c2])", | | "CHILD_1": { | | "ID":1, | | "OPERATOR":"TABLE FULL SCAN", | | "NAME":"t1", | | "EST.ROWS":1, | | "EST.TIME(us)":3, | | "output":"output([t1.c2], [t1.c1])" | | }, | | "CHILD_2": { | | "ID":2, | | "OPERATOR":"TABLE FULL SCAN", | | "NAME":"t2", | | "EST.ROWS":1, | | "EST.TIME(us)":3, | | "output":"output([t2.c2], [t2.c1])" | | } | | } | +----------------------------------------------------------+ 24 rows in set