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, DESCRIBE, and DESC are synonyms of each other.
Syntax
/*Obtain information about a table or column.*/
{EXPLAIN | DESCRIBE | DESC} table_name [column_name];
/*Obtain information about an SQL plan.*/
{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
| UPDATE statement
| MERGE statement
Parameters
| Parameter | Description |
|---|---|
| table_name | The name of the table. |
| column_name | The name of the column in the table. |
| INTO table_name | Specifies that the plan information generated by EXPLAIN is saved to the specified table. If you do not specify INTO table_name, the information is saved to the PLAN_TABLE table by default. |
| SET statement_id | Specifies a string to mark the current query, which facilitates subsequent queries for the plan information of this SQL statement. If you do not specify SET statement_id, an empty string is used as the information marker by default. |
| PRETTY | PRETTY_COLOR | Connects the parent nodes and child nodes in the plan tree with tree lines or colored tree lines, making it easier to read the execution plan. |
| explain_type | The type of the plan. |
| BASIC | Specifies the basic information of the plan, such as the operator ID, operator name, and referenced table name. |
| OUTLINE | Specifies that the plan information includes outline information. |
| EXTENDED | EXPLAIN generates additional information, including the input and output columns of each operator, the partition information of the accessed table, and the current filter information. If the current operator uses an index, the index column and query range are displayed. |
| EXTENDED_NOADDR | Displays the additional information in a simplified manner. |
| PARTITIONS | Displays the partition information. |
| TRADITIONAL | JSON | The output format of EXPLAIN.
|
| dml_statement | A 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 /* If an error occurs during the time format conversion, run the following command to reconfigure the TIMESTAMP format. */ obclient> ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'; 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 * FROM TABLE(DBMS_XPLAN.DISPLAY(table_name=>'test')); +--------------------------------------------------------------------------+ | COLUMN_VALUE | +--------------------------------------------------------------------------+ | ================================================= | | |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 setExplain the query plan and set the identifier information 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 |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 * FROM TABLE(DBMS_XPLAN.DISPLAY(table_name=>'test',statement_id=>'test2')); +--------------------------------------------------------------------------+ | COLUMN_VALUE | +--------------------------------------------------------------------------+ | ================================================= | | |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 setCreate tables
t101andt102.obclient> CREATE TABLE t101(c1 INT,c2 INT); Query OK, 0 rows affected obclient> CREATE TABLE t102(c1 INT,c2 INT); Query OK, 0 rows affectedGet information about table
t101.obclient> EXPLAIN t101; +-------+------------+------+-----+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +-------+------------+------+-----+---------+-------+ | C1 | NUMBER(38) | YES | NULL | NULL | NULL | | C2 | NUMBER(38) | YES | NULL | NULL | NULL | +-------+------------+------+-----+---------+-------+ 2 rows in setGet information about column
c2in tablet102.obclient> EXPLAIN t102 c2; +-------+------------+------+-----+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +-------+------------+------+-----+---------+-------+ | C2 | NUMBER(38) | YES | NULL | NULL | NULL | +-------+------------+------+-----+---------+-------+ 1 row in setOmit
explain_typeand return the execution plan information for theSELECTstatement.+------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH JOIN | |1 |8 | | | |1 |├─TABLE FULL SCAN|T102|1 |4 | | | |2 |└─TABLE FULL SCAN|T101|1 |4 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T101.C1], [T101.C2], [T102.C1], [T102.C2]), filter(nil), rowset=16 | | equal_conds([T101.C2 = T102.C2]), other_conds(nil) | | 1 - output([T102.C2], [T102.C1]), filter([T102.C1 > 4]), rowset=16 | | access([T102.C2], [T102.C1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([T102.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([T101.C2], [T101.C1]), filter(nil), rowset=16 | | access([T101.C2], [T101.C1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([T101.__pk_increment]), range(MIN ; MAX)always true | +------------------------------------------------------------------------------------+Output additional information using the
EXTENDED_NOADDRkeyword.obclient> EXPLAIN EXTENDED_NOADDR SELECT * FROM t101,t102 WHERE t101.c2=t102.c2 AND t102.c1 > 4; +------------------------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------------------------+ | ================================================= | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | ------------------------------------------------- | | |0 |HASH JOIN | |1 |8 | | | |1 |├─TABLE FULL SCAN|T102|1 |4 | | | |2 |└─TABLE FULL SCAN|T101|1 |4 | | | ================================================= | | Outputs & filters: | | ------------------------------------- | | 0 - output([T101.C1], [T101.C2], [T102.C1], [T102.C2]), filter(nil), rowset=16 | | equal_conds([T101.C2 = T102.C2]), other_conds(nil) | | 1 - output([T102.C2], [T102.C1]), filter([T102.C1 > 4]), rowset=16 | | access([T102.C2], [T102.C1]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([T102.__pk_increment]), range(MIN ; MAX)always true | | 2 - output([T101.C2], [T101.C1]), filter(nil), rowset=16 | | access([T101.C2], [T101.C1]), partitions(p0) | | is_index_back=false, is_global_index=false, | | range_key([T101.__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" ("SYS"."T102"@"SEL$1" "SYS"."T101"@"SEL$1")) | | USE_HASH(@"SEL$1" "SYS"."T101"@"SEL$1") | | FULL(@"SEL$1" "SYS"."T102"@"SEL$1") | | FULL(@"SEL$1" "SYS"."T101"@"SEL$1") | | OPTIMIZER_FEATURES_ENABLE('4.0.0.0') | | END_OUTLINE_DATA | | */ | | Optimization Info: | | ------------------------------------- | | T102: | | table_rows:1 | | physical_range_rows:1 | | logical_range_rows:1 | | index_back_rows:0 | | output_rows:0 | | table_dop:1 | | dop_method:Table DOP | | avaiable_index_name:[T102] | | stats version:0 | | dynamic sampling level:1 | | T101: | | 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:[T101] | | stats version:0 | | dynamic sampling level:1 | | Plan Type: | | LOCAL | | Note: | | Degree of Parallelisim is 1 because of table property | +------------------------------------------------------------------------------------+Display the execution plan for the
INSERTstatement inTRADITIONALformat.obclient> EXPLAIN FORMAT=TRADITIONAL INSERT INTO T101 VALUES(1,1); +----------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +----------------------------------------------------------------------------------------------------------------------------------------+ | ================================================== | | |ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)| | | -------------------------------------------------- | | |0 |DISTRIBUTED INSERT| |1 |13 | | | |1 |└─EXPRESSION | |1 |1 | | | ================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output(nil), filter(nil) | | columns([{T101: ({T101: (T101.__pk_increment, T101.C1, T101.C2)})}]), | | column_values([T_HIDDEN_PK], [column_conv(NUMBER,PS:(-1,0),NULL,__values.C1)], [column_conv(NUMBER,PS:(-1,0),NULL,__values.C2)]) | | 1 - output([__values.C1], [__values.C2]), filter(nil) | | values({1, 1}) | +----------------------------------------------------------------------------------------------------------------------------------------+Display the execution plan for the
SELECTstatement inJSONformat.obclient> EXPLAIN FORMAT=JSON SELECT * FROM t101,t102 WHERE t101.c2=t102.c2 AND t102.c1 > 4; +------------------------------------------------------------------+ | Query Plan | +------------------------------------------------------------------+ | { | | "ID":0, | | "OPERATOR":"HASH JOIN ", | | "NAME":"", | | "EST.ROWS":1, | | "EST.TIME(us)":8, | | "output":"output([T101.C1], [T101.C2], [T102.C1], [T102.C2])", | | "CHILD_1": { | | "ID":1, | | "OPERATOR":"TABLE FULL SCAN", | | "NAME":"T102", | | "EST.ROWS":1, | | "EST.TIME(us)":4, | | "output":"output([T102.C2], [T102.C1])" | | }, | | "CHILD_2": { | | "ID":2, | | "OPERATOR":"TABLE FULL SCAN", | | "NAME":"T101", | | "EST.ROWS":1, | | "EST.TIME(us)":4, | | "output":"output([T101.C2], [T101.C1])" | | } | | } | +------------------------------------------------------------------+