説明
このステートメントは、SQL文の実行計画を解釈するために使用されます。SELECT、DELETE、INSERT、REPLACE、または UPDATE 文が対象となります。
EXPLAIN は DESCRIBE、DESC の同義語です。
構文
{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
パラメータの説明
パラメータ |
説明 |
|---|---|
| INTO table_name | EXPLAIN の計画情報を指定したテーブルに保存します。INTO table_name を指定しない場合、デフォルトで PLAN_TABLE テーブルにクエリされます。 |
| SET statement_id | 現在のクエリに文字列タグを設定し、後からそのSQLの計画情報を検索しやすくします。SET statement_id を指定しない場合、デフォルトで空文字列が情報タグとして使用されます。 |
| PRETTY | PRETTY_COLOR | 計画ツリー内の親ノードと子ノードをツリーラインまたはカラーツリーラインで結び、実行計画をより読みやすく表示します。 |
| BASIC | 出力計画の基本情報（演算子ID、演算子名、参照されるテーブル名など）を指定します。 |
| OUTLINE | 出力する計画情報に OUTLINE 情報を含めることを指定します。 |
| EXTENDED | EXPLAIN が追加情報を生成します。これには、各演算子の入力列と出力列、アクセスするテーブルのパーティション情報、現在使用中のフィルター情報、現在の演算子がインデックスを使用している場合は、使用されているインデックス列と抽出されたQuery Rangeが含まれます。 |
| EXTENDED_NOADDR | 追加情報を簡潔な方法で表示します。 |
| PARTITIONS | パーティション関連情報を表示します。 |
| FORMAT = {TRADITIONAL| JSON} | EXPLAIN の出力形式を指定します：
|
| dml_statement | DML文。 |
例
クエリ計画を解釈し、計画情報を指定したテーブル
testに保存します。
/* テーブル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); Query OK, 0 rows affected
/* クエリプランを解釈し、その情報を指定された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
|ID|OPERATOR |NAME|EST.ROWS|EST.TIME(us)|
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 set
* クエリ計画を解釈し、クエリに識別情報を設定します。
```shell
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 set
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 |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 | +------------------------------------------------------------------------------------+EXTENDED_NOADDRキーワードを使用して追加情報を出力します。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 | +------------------------------------------------------------------------------------+TRADITIONAL形式の実行計画を表示します。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 | +------------------------------------------------------------------------------------+JSON形式の実行計画を表示します。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])" | | } | | } | +----------------------------------------------------------+