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的相关选下如下:
SQL语句的唯一标识符。通过使用SQL语句的标识符,可以向一个计划表中存入多条SQL语句。
存储执行计划的计划表的名称。此表必须已经存在并且与标准表结构一致。如果没有指定计划表名称,EXPLAIN PLAN会尝试使用表名PLAN_TABLE.
你想要知道其执行计划的那条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')
分享到:
相关推荐
详细讲解在Oracle中如何使用explain_plan,值得参考和收藏学习。
1.如何分析SQL语句 2.选用适合的ORACLE优化器 3.用EXPLAIN PLAN 分析SQL语句 4.使用TKPROF 工具来查询SQL性能状态 5.表分区的应用
RBO遵循简单的分级方法学,使用15种级别要点,当接收到查询,优化器将评估使用到的要点数目, 然后选择最佳级别(最少的数量)的执行路径来运行查询。 CBO尝试找到最低成本的访问数据的方法,为了最大的吞吐量或最快的...
SQL> explain plan for alter index idx_policy_id2 rebuild online; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -----------------------------------------------------...
截取部分题目如下,有答案: 1. 解释冷备份和热备份的不同点以及各自的优点 2. 你必须利用备份恢复数据库,但是你没有...17. 如何生成explain plan? 18. 如何增加buffer cache的命中率? 19. ORA-01555的应对方法?
§12.8.5 解释计划(Explain Plan)策略 156 §12.8.6 AUTOTRACE 实用程序 157 第13章 数据访问方法 160 §13.1 使用索引的访问方法 161 §13.1.1 何时创建索引 161 §13.1.2 索引列和表达式的选择 161 §13.1.3 选择...
10.2.2 EXPLAIN PLAN FOR与DBMS_XPLAN 483 10.2.3 通过AWR获取SQL执行计划 487 10.3 捕获问题SQL解决过度CPU消耗问题 488 10.3.1 使用vmstat检查系统当前情况 488 10.3.2 使用TOP工具辅助诊断 489 10.3.3...
通过分析,我们就可以知道ORACLE是怎样连接表,使用什么方式扫描表(索引扫描或全表扫描),以及使用到的索引名称 按照从里到外,从上到下的次序解读分析的结果 EXPLAIN PLAN的分析结果是用缩进的格式...
第一部分 Oracle SQL*PLUS基础 23 第一章 Oracle数据库基础 23 §1.1 理解关系数据库系统(RDBMS) 23 §1.1.1 关系模型 23 §1.1.2 Codd十二法则 24 §1.2 关系数据库系统(RDBMS)的组成 24 §1.2.1 RDBMS 内核 24...
1 性能优化 1.1 避免频繁 commit,尤其...说明:除非是单据的单号,要求必须是唯一,并且依据流水号不可以跳号,不然在大量交易的表格中,不在乎跳耗时,要取得唯一的Primary Key 建议使用Oracle Sequence这样速度会较...
非常容易对SQL语句的分析其执行计划:单击工具栏上的 按钮就可以看到Explain Plan的结果 支持部分SQL*Plus命令,可以方便的执行大量的为SQL*Plus编写的数据库脚本 可以通过SQLab xpert来对当前SQL语句...