Oracle EXPLAIN PLAN用法
1.SQL语句的执行计划
使用EXPLAIN PLAN语句来确定Oracle数据库下指定SQL语句的执行计划,这个语句插入每一步执行计划的行描述到指定表中。你也可使用EXPLAIN PLAN语句作为SQL跟踪工具的一部分。
EXPLAIN PLAN命令的语法如下:
EXPLAIN PLAN
[ SET STATEMENT_ID = string ]
[ INTO [ schema. ] table_name [ @ dblink ] ]
FOR sql_statement ;
EXPLAIN PLAN的相关选下如下:
- STATEMENT_ID
SQL语句的唯一标识符。通过使用SQL语句的标识符,可以向一个计划表中存入多条SQL语句。
- TABLE_NAME
存储执行计划的计划表的名称。此表必须已经存在并且与标准表结构一致。如果没有指定计划表名称,EXPLAIN PLAN会尝试使用表名PLAN_TABLE.
-
SQL_STATEMENT
你想要知道其执行计划的那条SQL语句。这条SQL语句必须是有效的。并且你也必须有足够的权限来执行它。这条SQL语句可以含有绑定变量。
2.计划表
默认情况下,Oracle会将执行计划插入如到一张名为PLAN_TABLE的表中。可以使用脚本utlexplain.sql来创建自己的计划表。这个脚本位于Oracle软件安装目录的子目录$ORACLE_HMOE/rmdbs/admin/中。然而,从Oracle 10g开始,Oracle会创建一个全局临时表PLAN_TABLE供所有用户使用,所以通常情况下不需要创建自己的计划表。由于此默认的计划表是一个全局临时表,所以你无法看到其他会话插入的执行计划,你的执行计划也会随着自己会话的结束而自动消失。
(计划表)
列名 |
类型 |
描述 |
---|---|---|
STATEMENT_ID |
VARCHAR2(30) |
在EXPLAIN PLAN的SET STATEMENT_ID子句提供的SQL语句的唯一标志符。 |
PLAN_ID |
NUMBER |
执行计划的在全局表plan_table中的唯一标识符 |
TIMESTAMP |
DATE |
EXPLAN PLAN语句执行的日期和时间 |
REMARKS |
VARCHAR2(80) |
注释 |
OPERATION |
VARCHAR2(30) |
执行的操作类型。如TABLE ACCESS,SORT或HASH JOIN |
OPTIONS |
VARCHAR2(225) |
操作的附加信息,例如,以TABLE SCAN为例,选项可能是FULL或BY ROWID |
OBJECT_NODE |
VARCHAR2(128) |
如果是分布式查询,这一列表示用于引用对象的数据库链接名称。如果并行查询,它的值可能对应一个临时的结果集。 |
OBJECT_OWNER |
VARCHAR2(30) |
对象的名字 |
OBJECT_NAME |
VARCHAR2(30) |
对象名称 |
OBJECT_ALIAS |
VARCHAR2(65) |
对象的别名 |
OBJECT_INSTANCE |
NUMERIC |
对象在SQL语句中的位置 |
OBJECT_TYPE |
VARCHAR2(30) |
对象的类型(表,索引等) |
OPTIMIZER |
VARCHAR2(255) |
解释SQL语句时生效的优化器 |
SEARCH_COLUMNS |
NUMBERIC |
未使用 |
ID |
NUMERIC |
执行计划的ID号 |
PARENT_ID |
NUMERIC |
上一个步骤的ID号 |
DEPTH |
NUMERIC |
操作的深度 |
POSITION |
NUMERIC |
如果两个步骤有相同的父步骤,有更低POSITION值的步骤将被先执行 |
COST |
NUMERIC |
优化器估算出来的此操作的相对成本 |
CARDINALITY |
NUMERIC |
优化器预期这一步将饭后的记录数 |
BYTES |
NUMERIC |
预计这一步将返回的字节数 |
OTHER_TAG |
VARCHAR2(255) |
标识OTHER列中的值的类型。 |
PARTITION_START |
VARCHAR2(255) |
访问的分区范围的起始分区 |
PARTITION_STOP |
VARCHAR2(255) |
访问的分区范围的结束分区 |
PARTITION_ID |
NUMERIC |
计算PARTITION_START和PARTITION_STOP列的值对的ID |
OTHER |
LONG |
对于分布式查询,这列可能是包含发往远程数据库的SQL语句的文本。对于并行查询,它比啊是并行从属进程执行的SQL语句。 |
DISTRIBUTION |
VARCHAR2(30) |
描述记录是如何从一组并行查询从属进程分配到后续的“消费者”从属进程的。 |
CPU_COST |
NUMERIC |
估算出来的操作的CPU成本 |
IO_COST |
NUMERIC |
估算出来的的操作的IO成本 |
TEMP_SPACE |
NUMERIC |
估算出来的这一步操作所使用的临时存储的空间大小 |
ACCESS_PREDICATES |
VARCHAR2(4000) |
SQL语句中,确定如何在当前步骤中提取记录的子句。 |
FILTER_PREDICATES |
VARCHAR2(4000) |
SQL语句中确定对见记录进行过滤的子句路,如WHERE子句在非索引列上的条件。 |
PROJECTION |
VARCHAR2(4000) |
决定将返回的记录的子句,通常是SELECT后面的字段列表 |
TIME |
NUMBER(20,2) |
优化器为这一步执行估算的时间消耗 |
QBLOCK_NAME |
VARCHAR2(30) |
查询块的唯一标识符。 |
(常见的执行计划操作)
操 作 |
选 项 |
描 述 |
---|---|---|
表的访问路径 |
||
TABLE ACCESS |
FULL |
全表扫描,他会读取表中的每一条记录(严格地说,它读取表的高水位以内的每个数据块) |
CLUSTER |
通过索引簇的键来访问数据 |
|
HASH |
通过散键列来访问表中匹配特定的散列值的一条或多条记录 |
|
BY INDEX ROWID |
通过指定ROWID来访问表中的单条记录。ROWID是访问单条记录的最快捷的方式。通常,ROWID的信息都是有一个相关的索引检索而来 |
|
BY USER ROWID |
通过提供一个绑定变量、字面变量或WHERE CURRENT OF CURSOR子句来通过ROWID进行访问 |
|
BY GLOBAL INDEX ROWID |
通过由全局分区索引获得的ROWID进行访问 |
|
BY LOCAL INDEX ROWID |
通过本地分区索获得的ROWID进行访问 |
|
SAMPLE |
使用SAMPLE子句得到结果集的一个经过采样的子集 |
|
EXTERNAL TABLE ACCESS |
|
访问一张外部表 |
RESULT CACHE |
|
这个SQL结果集可能来自结果集缓存 |
MAT_VIEW REWIRTE ACCESS |
|
SQL语句被重写以利用物化视图 |
索引操作 |
||
ADN_EQUAL |
|
合并来自一个或多个索引扫描的结果集 |
INDEX |
UNIQUE SCAN |
只返回一条记录的地址(ROWID)的索引检索 |
RANGE SCAN |
返回多条记录的ROWID的索引检索。之所以可以这样返回,是因为是非唯一索引或是使用了区间操作符(例如,>) |
|
FULL SCAN |
按照索引的顺序扫描整个索引 |
|
KIP SCAN |
搜索碎索引键中哦非前导列的索引扫描 |
|
FULL SCAN(MAX/MIN) |
检索最高或最低的索引条目 |
|
FAST FULL SCAN |
按照块顺序扫描索引的每个条目,可能会使用多块读取 |
|
DOMAIN INDEX |
|
域索引(用户定义的索引类型)检索 |
位图操作 |
||
BITMAP |
CONVERSION |
将ROWID转换成位图或将位图转换成ROWID |
INDEX |
从位图中提取一个值或一个范围的值 |
|
MERGE |
合并多个位图 |
|
MINUS |
从一个位图中减去另一个位图 |
|
OR |
按位(bit-wise)对两个位图做OR操作 |
|
表连接 |
||
CONNECT BY |
|
对前一个步骤的输出结果执行一个层次化的自联接操作 |
MERGE JOIN |
|
对前一个步骤的输出结果执行一次合并联接 |
NESTED LOOPS |
|
对前一个步骤执行嵌套循环联接。对于上层的结果集中的每一行,都会扫描下层的结果集以找到匹配的记录 |
HASH JOIN |
|
对两个记录源(row source)进行散列联接 |
任何连接操作 |
OUTER |
此连接为外联接 |
任何连接操作 |
ANTI |
此连接为反联接 |
任何连接操作 |
SEMI |
此连接为半联接 |
任何连接操作 |
CARTESIAN |
一个结果集中的每一条记录与另一个结果中的每一条记录进行联接 |
集合操作 |
||
CONCATENATION |
|
与显示指定一个UNION语句一样,多个结果集被按照同样的方式做合并。它通常会发生在对索引列使用OR语句时 |
INTERSECTION |
|
对两个结果集进行比较,只返回在两个结果集中都存在的记录。通常只有显式地使用INTERSECT子句,这个操作才会发生 |
MINUS |
|
除在第二个结果中出现过的记录外,返回一个结果中的所有记录。它是使用MINUS集合操作符的结果 |
UNION-ALL |
|
对两个结果集进行合并,并返回两个结果集中的所有记录 |
UNION |
|
对两个结果集进行合并,并返回两个结果集中的所有记录,但是不返回重复记录 |
VIEW |
|
要么访问一个视图定义,要么创建一个临时表来存储结果集 |
其他杂项 |
||
FOR UPDATE |
|
由于FOR UPDATE子句的原因,返回的记录都会被锁住 |
COLLECTION ITERATOR |
各种 |
从一个表函数提取记录的操作(也就是 FROM TABLE()) |
FAST DUAL |
|
访问DUAL表,以避免从缓冲区高数缓存中读取 |
FILTER |
|
从结果集中排除掉不匹配给定选取条件的记录 |
REMOTE |
|
通过数据库链接访问一个外部的数据库 |
FIRST ROW |
|
获取查询的第一条记录 |
SEQUENCE |
|
使用Oracle序列号生成器来获得一个唯一的序列号 |
INLIST ITERATOR |
|
对于IN列表中的每个值都执行一次下一个操作 |
LOAD AS SELECT |
|
表示这是一个基于SELECT语句的直接路径INSERT操作 |
FIXED TABLE |
|
访问固定的(X$或V$)表 |
FIXED INDEX |
|
访问固定表X$上的索引 |
WINDOW |
BUFFER |
支持分析函数(如OVER())的内部操作 |
WINDOW |
SORT [PUSHED]RANK |
分析函数需要为实现RANK()函数执行一次排序操作 |
分区操作 |
||
PARTITION |
SINGLE |
访问单个分区 |
ITERATOR |
访问多个分区 |
|
ALL |
访问所有分区 |
|
INLIST |
基于IN列表中的值来访问多个分区 |
|
汇总操作 |
||
COUNT |
|
为了满足COUNT()函数而计算结果集中的记录数 |
COUNT |
STOPKEY |
计算结果集中的记录数,当达到一定数量后就停止处理。这通常发生在使用了WHERE子句,并指定了一个最大值ROWNUM(例如,WHERE ROWNUM<=10)的情况下 |
BUFFER |
SORT |
对临时结果集做的一次内存排序 |
HASH |
GROUP BY |
使用散列操作而不是排序操作实现GROUP BY |
INLIST |
ITERATOR |
对于IN列表中的每个值都实现一次子操作 |
SORT |
ORDER BY |
为了满足ORDER BY子句而对结果集进行排序 |
AGGREGATE |
当在已经分好组的数据上使用分组函数是会出现此操作 |
|
JOIN |
为了准备合并连接而对记录进行排序 |
|
UNIQUE |
排除重复记录的排序操作,通常是使用DISTINCT子句的结果 |
|
GROUP BY |
为GROUP BY子句对结果集进行排序分组 |
|
GROUP BY NOSORT |
不需要进行排序操作的GROUP BY操作 |
|
GROUP BY ROLLUP |
含有ROLLUP选项的GROUP BY操作 |
|
GROUP BY CUBE |
含有CUBE选项的GROUP BY操作 |
3.查看执行计划
当SQL语句的执行计划生成以后,我们就可以去查看SQL语句的执行计划了。有两种方法可以查看执行计划:直接查看计划表和DBMS_XPLAN.DISPALY表函数。
第一种方法:
为了更好地理解计划表中的数据,需要针对计划表做层次查询。通过SELECT语句的 CONNECT BY子句对PARENT_ID和ID两列进行自连接。这种查询语句通常的写法如下:
select rtrim(lpad(' ', 2 * level) || rtrim(operation) || ' ' || rtrim(options)) description,
object_owner,
object_name,
cost,
cardinality,
bytes,
io_cost,
cpu_cost
from plan_table
connect by prior id = parent_id
start with id = 0
第二种方法:
与手工查询计划表相比,使用DBMS_XPLAN通常可以更好的结果,它的语法更加简单,还提供了多种有用的输出格式,并且可以利用缓存的执行计划统计信息。
调用DBMS_XPLAN函数最简单的方法就是使用 select * from table()语句,如下面的语句:
select * from table(dbms_xplan.function(options));
最常用的两个DBMS_XPLAN函数:
DBMS_XPLAN.DISPLAY(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL',
filter_preds IN VARCHAR2 DEFAULT NULL);
DBMS_XPLAN.DISPLAY_CURSOR(
sql_id IN VARCHAR2 DEFAULT NULL,
child_number IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');
4.Examples
创建emp_test表
create table emp_test as select *from emp;
create unique index EMP_TEST_U1 on EMP_TEST (empno);
create index emp_test_n1 on EMP_TEST (ename);
通过EXPLAIN PLAN语句,插入指定SQL语句的执行计划。
SQL> explain plan set statement_id ='plan_sql_id' for select * from emp_test t where t.ename='SCOTT';
Explained
手动查询计划表查看计划:
SQL> select rtrim(lpad(' ', 2 * level) || rtrim(operation) || ' ' || rtrim(options)) description,
2 object_owner,
3 object_name,
4 cost,
5 cardinality,
6 bytes,
7 io_cost,
8 cpu_cost
9 from plan_table
10 connect by prior id = parent_id
11 start with id = 0;
DESCRIPTION OBJECT_OWNER OBJECT_NAME COST CARDINALITY BYTES IO_COST CPU_COST
-------------------------------- ------------- ------------- ------ ----------- ----- ------- ---------
SELECT STATEMENT 2 1 38 2 14733
TABLE ACCESS BY INDEX ROWID SCOTT EMP_TEST 2 1 38 2 14733
INDEX RANGE SCAN SCOTT EMP_TEST_N1 1 1 1 7321
调用DBMS_XPLAN函数查看:
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1758671844
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEST | 1 | 38 | 2 (0)|
|* 2 | INDEX RANGE SCAN | EMP_TEST_N1 | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ENAME"='SCOTT')