ORACLE数据库查看执行计划

ORACLE数据库查看执⾏计划
基于ORACLE的应⽤系统很多性能问题，是由应⽤系统SQL性能低劣引起的，所以，SQL的性能优化很重要，分析与优化SQL的性能我们⼀般通过查看该SQL的执⾏计划，本⽂就如何看懂执⾏计划，以及如何通过分析执⾏计划对SQL进⾏优化做相应说明。

⼀、什么是执⾏计划（explain plan）
执⾏计划：⼀条查询语句在ORACLE中的执⾏过程或访问路径的描述。

⼆、如何查看执⾏计划
1: 在PL/SQL下按F5查看执⾏计划。

第三⽅⼯具toad等。

很多⼈以为PL/SQL的执⾏计划只能看到基数、优化器、耗费等基本信息，其实这个可以在PL/SQL⼯具⾥⾯设置的。

可以看到很多其它信息，如下所⽰
2: 在SQL*PLUS(PL/SQL的命令窗⼝和SQL窗⼝均可)下执⾏下⾯步骤
SQL>EXPLAIN PLAN FOR
SELECT * FROM SCOTT.EMP; --要解析的SQL脚本
SQL>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
3: 在SQL*PLUS下(有些命令在PL/SQL下⽆效)执⾏如下命令：
SQL>SET TIMING ON --控制显⽰执⾏时间统计数据
SQL>SET AUTOTRACE ON EXPLAIN --这样设置包含执⾏计划、脚本数据输出，没有统计信息
SQL>执⾏需要查看执⾏计划的SQL语句
SQL>SET AUTOTRACE OFF --不⽣成AUTOTRACE报告，这是缺省模式
SQL> SET AUTOTRACE ON --这样设置包含执⾏计划、统计信息、以及脚本数据输出
SQL>执⾏需要查看执⾏计划的SQL语句
SQL>SET AUTOTRACE OFF
SQL> SET AUTOTRACE TRACEONLY --这样设置会有执⾏计划、统计信息，不会有脚本数据输出
SQL>执⾏需要查看执⾏计划的SQL语句
SQL>SET AUTOTRACE TRACEONLY STAT --这样设置只包含有统计信息
SQL>执⾏需要查看执⾏计划的SQL语句
SET AUTOT[RACE] {ON | OFF | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
参考⽂档：SQLPlus User’s Guide and Reference Release 11.1
注意：PL/SQL Developer ⼯具并不完全⽀持所有的SQL*Plus命令，像SET AUTOTRACE ON 就如此，在PL/SQL Developer⼯具下执⾏此命令会报错
SQL> SET AUTOTRACE ON;
Cannot SET AUTOTRACE
4：SQL_TRACE可以作为参数在全局启⽤，也可以通过命令形式在具体SESSION启⽤
4.1 在全局启⽤，在参数⽂件（pfile/spfile)中指定SQL_TRACE =true，在全局启⽤SQL_TRACE时会导致所有进程活动被跟踪，包括后台进程以及⽤户进程，通常会导致⽐较严重的性能问题，所以在⽣产环境要谨慎使⽤。

提⽰：通过在全局启⽤SQL_TRACE，我们可以跟踪到所有后台进程的活动，很多在⽂档中的抽象说明，通过跟踪⽂件的实时变化，我们可以清晰的看到各个进程间的紧密协调。

4.2在当前SESSION级别设置，通过跟踪当前进程可以发现当前操作的后台数据库递归活动（这在研究数据库新特性时尤其有效），研究SQL执⾏时，发现后台
错误等。

SQL> ALTER SESSION SET SQL_TRACE=TRUE;
SQL> SELECT * FROM SCOTT.EMP;
SQL> ALTER SESSION SET SQL_TRACE =FALSE;
那么此时如何查看相关信息？不管你在SQL*PLUS抑或PL/SQL DEVELOPER⼯具⾥⾯执⾏上⾯脚本过后都看不到什么信息，你可以通过下⾯脚本查询到trace⽇志信息
SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T
TKPROF的帮助信息如下
TKPROF 选项
选项说明
TRACEFILE 跟踪输出⽂件的名称
OUTPUTFILE 已设置格式的⽂件的名称
SORT=option 语句的排序顺序
PRINT=n 打印前 n 个语句
EXPLAIN=user/password 以指定的⽤户名运⾏ EXPLAIN PLAN
INSERT=filename ⽣成 INSERT 语句
SYS=NO 忽略作为⽤户 sys 运⾏的递归 SQL 语句
AGGREGATE=[Y|N] 如果指定 AGGREGATE = NO TKPROF 不聚集相同
SQL ⽂本的多个⽤户
RECORD=filename 记录在跟踪⽂件中发现的语句
TABLE=schema.tablename 将执⾏计划放⼊指定的表⽽不是缺省的PLAN_TABLE
可以在操作系统中键⼊ tkprof 以获得所有可⽤选项和输出的列表
注排序选项有
排序选项说明
prscnt execnt fchcnt 调⽤分析执⾏提取的次数
prscpu execpu fchcpu 分析执⾏提取所占⽤的 CPU 时间
prsela exela fchela 分析执⾏提取所占⽤的时间
prsdsk exedsk fchdsk 分析执⾏提取期间的磁盘读取次数
prsqry exeqry fchqry 分析执⾏提取期间⽤于持续读取的缓冲区数
prscu execu fchcu 分析执⾏提取期间⽤于当前读取的缓冲区数
prsmis exemis 分析执⾏期间库⾼速缓存未命中的次数
exerow fchrow 分析执⾏期间处理的⾏数
userid 分析游标的⽤户的⽤户 ID
TKPROF 统计数据
Count: 执⾏调⽤数
CPU: CPU 的使⽤秒数
Elapsed: 总共⽤去的时间
Disk: 物理读取次数
Query: 持续读取的逻辑读取数
Current: 当前模式下的逻辑读取数
Rows: 已处理⾏数
TKPROF 统计信息
统计含义
Count 分析或执⾏语句的次数以及为语句发出的提取调⽤数
CPU 每个阶段的处理时间以秒为单位如果在共享池中找到该语句对于分析阶段为 0
Elapsed 占⽤时间以秒为单位通常不是⾮常有⽤因为其它进程影响占⽤时间
Disk 从数据库⽂件读取的物理数据块如果该数据被缓冲则该统计可能很低
Query 为持续读取检索的逻辑缓冲区通常⽤于 SELECT 语句
Current 在当前模式下检索的逻辑缓冲区通常⽤于 DML 语句
Rows 外部语句所处理的⾏对于 SELECT 语句在提取阶段显⽰它对于 DML 语句在执⾏阶段显⽰它
Query 和Current 的总和为所访问的逻辑缓冲区的总数
执⾏下⾯命令：tkprof D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc h:\out.txtoutputfile explain=etl/etl 执⾏上⾯命令后，可以查看⽣成的⽂本⽂件
View Code
4.3跟踪其它⽤户的进程，在很多时候我们需要跟踪其它⽤户的进程，⽽不是当前⽤户，可以通过ORACLE提供的系统包
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION来完成。

例如：
SELECT SID, SERIAL#, USERNAME FROM V$SESSION WHERE USERNAME = 'ETL'
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,TRUE);
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(61,76,FALSE);
5 利⽤10046事件
ALTER SESSION SET TRACEFILE_IDENTIFIER = 10046;
ALTER SESSION SET EVENTS='10046 trace name context forever, level 8';
SELECT * FROM SCOTT.EMP;
ALTER SESSION SET EVENTS ='10046 trace name context off';
然后你可以⽤脚本查看追踪⽂件的位置
SELECT T.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM
( SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# =1
AND S.SID = M.SID
AND P.ADDR = S.PADDR
) P,
( SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE ='thread'
AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE))
) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T
查询结果为wgods_ora_28279.trc⽂件，但是去相应⽬录却没有找到对应的追踪⽂件，⽽是如下trace⽂件：wgods_ora_28279_10046.trc
6 利⽤10053事件
有点类似10046，在此略过、
7 系统视图
通过下⾯⼀些系统视图，你可以看到⼀些零散的执⾏计划的相关信息，有兴趣的话可以多去研究⼀下。

SELECT * FROM V$SQL_PLAN
SELECT * FROM V$RSRC_PLAN_CPU_MTH
SELECT * FROM V$SQL_PLAN_STATISTICS
SELECT * FROM V$SQL_PLAN_STATISTICS_ALL
SELECT * FROM V$SQLAREA_PLAN_HASH
SELECT * FROM V$RSRC_PLAN_HISTORY
三、看懂执⾏计划
1.执⾏顺序
执⾏顺序的原则是：由上⾄下，从右向左
由上⾄下：在执⾏计划中⼀般含有多个节点，相同级别(或并列)的节点，靠上的优先执⾏，靠下的后执⾏
从右向左：在某个节点下还存在多个⼦节点，先从最靠右的⼦节点开始执⾏。

当然，你在PL/SQL⼯具中也可以通过它提供的功能来查看执⾏顺序。

如下图所⽰：
2.执⾏计划中字段解释
SQL>
名词解释：
recursive calls 递归调⽤
db block gets 从buffer cache中读取的block的数量当前请求的块数⽬，当前模式块意思就是在操作中正好提取的块数⽬，⽽不是在⼀致性读的情况下⽽产⽣的正常情况下，⼀个查询提取的块是在查询查询开始的那个时间点上存在的数据库，当前块是在这个时候存在数据块，⽽不是这个时间点之前或者之后的的数据块数⽬。

consistent gets 从buffer cache中读取的undo数据的block的数量数据请求总数在回滚段Buffer中的数据⼀致性读所需要的数据块，，这⾥的概念是在你处理你这个操作的时侯需要在⼀致性读状态上处理多个块，这些块产⽣的主要原因是因为你在查询过程中，由于其它会话对数据块进⾏操作，⽽对所要查询的块有了修改，但是由于我们的查询是在这些修改之前调⽤的，所要需要对回滚段中的数据块的前映像进⾏查询，以保证数据的⼀致性。

这样就产⽣了⼀致性读。

physical reads 物理读就是从磁盘上读取数据块的数量。

其产⽣的主要原因是：
1：在数据库⾼速缓存中不存在这些块。

2：全表扫描
3：磁盘排序
redo size DML⽣成的redo的⼤⼩
sorts (memory) 在内存执⾏的排序量
sorts (disk) 在磁盘执⾏的排序量
2091 bytes sent via SQL*Net to client 从SQL*Net向客户端发送了2091字节的数据
416 bytes received via SQL*Net from client 客户端向SQL*Net发送了416字节的数据。

参考⽂档：SQLPlus User’s Guide and Reference Release 11.1
db block gets 、 consistent gets 、 physical reads这三者的关系可以概括为：逻辑读指的是ORACLE从内存读到的数据块块数量，⼀般来说是：
consistent gets + db block gets. 当在内存中找不到所需要的数据块的话，就需要从磁盘中获取，于是就产⽣了物理读。

3.具体内容查看
1> Plan hash Value
这⼀⾏是这⼀条语句的的hash值，我们知道ORACLE对每⼀条ORACLE语句产⽣的执⾏计划放在SHARE POOL⾥⾯，第⼀次要经过硬解析，产⽣hash值。

下次再执⾏时⽐较hash值，如果相同就不会执⾏硬解析。

2> COST
COST没有单位，是⼀个相对值，是SQL以CBO⽅式解析执⾏计划时，供ORACLE来评估CBO成本，选择执⾏计划⽤的。

没有明确的含义，但是在对⽐是就⾮常有⽤。

公式：COST=（Single Block I/O COST + MultiBlock I/O Cost + CPU Cost）/ Sreadtim
3> 对上⾯执⾏计划列字段的解释：
Id: 执⾏序列，但不是执⾏的先后顺序。

执⾏的先后根据Operation缩进来判断（采⽤最右最上最先执⾏的原则看层次关系，在同⼀级如果某个动作没有⼦ID就最先执⾏。

⼀般按缩进长度来判断，缩进最⼤的最先执⾏，如果有2⾏缩进⼀样，那么就先执⾏上⾯的。

）
Operation：当前操作的内容。

Name：操作对象
Rows：也就是10g版本以前的Cardinality(基数)，Oracle估计当前操作的返回结果集⾏数。

Bytes：表⽰执⾏该步骤后返回的字节数。

Cost（CPU）：表⽰执⾏到该步骤的⼀个执⾏成本，⽤于说明SQL执⾏的代价。

Time：Oracle 估计当前操作的时间。

4.谓词说明：
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("B"."MGR" IS NOT NULL)
4 - access("A"."EMPNO" = "B"."MGR")
Access: 表⽰这个谓词条件的值将会影响数据的访问路劲（全表扫描还是索引）。

Filter：表⽰谓词条件的值不会影响数据的访问路劲，只起过滤的作⽤。

在谓词中主要注意access，要考虑谓词的条件，使⽤的访问路径是否正确。

5、动态分析
如果在执⾏计划中有如下提⽰：
Note
------------
-dynamic sampling used for the statement
这提⽰⽤户CBO当前使⽤的技术，需要⽤户在分析计划时考虑到这些因素。

当出现这个提⽰，说明当前表使⽤了动态采样。

我们从⽽推断这个表可能没有做过分析。

这⾥会出现两种情况：
（1）如果表没有做过分析，那么CBO可以通过动态采样的⽅式来获取分析数据，也可以或者正确的执⾏计划。

（2）如果表分析过，但是分析信息过旧，这时CBO就不会在使⽤动态采样，⽽是使⽤这些旧的分析数据，从⽽可能导致错误的执⾏计划。

四、表访问⽅式
1.Full Table Scan (FTS) 全表扫描
2.Index Lookup 索引扫描
There are 5 methods of index lookup:
index unique scan --索引唯⼀扫描
通过唯⼀索引查找⼀个数值经常返回单个ROWID，如果存在UNIQUE或PRIMARY KEY约束（它保证了语句只存取单⾏的话），ORACLE
经常实现唯⼀性扫描
Method for looking up a single key value via a unique index. always returns a single value, You must supply AT LEAST the leading column of the index to access data via the index.
index range scan --索引局部扫描
Index range scan is a method for accessing a range values of a particular column. AT LEAST the leading column of the index must be supplied to access data via the index. Can be used for range operations (e.g. > < <> >= <= between) .
使⽤⼀个索引存取多⾏数据，在唯⼀索引上使⽤索引范围扫描的典型情况是在谓词(WHERE 限制条件)中使⽤了范围操作符号（如>, < <>, >=, <=,BWTEEN)
index full scan --索引全局扫描
Full index scans are only available in the CBO as otherwise we are unable to determine whether a full scan would be a good idea or not. We choose an index Full Scan when we have statistics that indicate that it is going to be more efficient than a Full table scan and a sort. For example we may do a Full index scan when we do an unbounded scan of an index and want the data to be ordered in the index order. index fast full scan --索引快速全局扫描，不带order by情况下常发⽣
Scans all the block in the index, Rows are not returned in sorted order, Introduced in 7.3 and requires V733_PLANS_ENABLED=TRUE and CBO, may be hinted using INDEX_FFS hint, uses multiblock i/o, can be executed in parallel, can be used to access second column of concatenated indexes. This is because we are selecting all of the index.
index skip scan --索引跳跃扫描，where条件列是⾮索引的前提情况下常发⽣
Index skip scan finds rows even if the column is not the leading column of a concatenated index. It skips the first column(s) during the search.
3.Rowid 物理ID扫描
This is the quickest access method available.Oracle retrieves the specified block and extracts the rows it is interested in. --Rowid扫描是最快的访问数据⽅式。