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')

 

 

  • 0
    点赞
  • 11
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值