Oracle EXPLAIN PLAN用法

zgkfxwqakc

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)
　　执行的操作类型。如TABLEACCESS,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')