一、什么是执行计划（explain plan） 执行计划：一条
查询语句在ORACLE中的执行过程或访问路径的描述。 二、如何查看执行计划 1: 在PL/
sql下按F5查看执行计划。第三方工具toad等。 很多人以为PL/
sql的执行计划只能看到基数、优化器、耗费等基本信息，其实这个可以在PL/
sql工具里面设置的。可以看到很多其它信息，如下所示 clip_image002 2: 在
sql*PLUS(PL/
sql的命令窗口和
sql窗口均可)下执行下面步骤 复制
代码 代码如下:
sql>EXPLAIN PLAN FOR SELECT * FROM SCOTT.EMP; --要解析的
sql脚本
sql>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); clip_image004 clip_image006 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 clip_image008 注意：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 V.NAME ='thread' AND (V.VALUE = 0 OR T.THREAD# = TO_NUMBER(V.VALUE)) ) I,(SELECT VALUE FROM V$PARAMETER WHERE NAME='user_dump_dest') T clip_image010 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
调用分析执行
提取的
次数 prs
cpu exe
cpu fch
cpu 分析执行
提取所占用的
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 clip_image012 执行上面命令后，可以查看
生成的文本
文件 复制
代码 代码如下: TKPROF: Release 10.2.0.1.0 - Production on 星期三 5月 23 16:56:41 2012 Copyright (c) 1982,2005,Oracle. All rights reserved. Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc Sort options: default ******************************************************************************** count = number of times OCI procedure was executed
cpu =
cpu time in seconds executing elapsed = elapsed time in seconds executing disk = number of physical reads of buffers from disk query = number of buffers gotten for consistent read current = number of buffers gotten in current mode (usually for update) rows = number of rows processed by the fetch or execute call ******************************************************************************** ALTER SESSION SET
sql_TRACE = TRUE call count
cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 1 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: 89 (ETL) ******************************************************************************** begin :id := sys.dbms_transaction.local_transaction_id; end; call count
cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 2 0.00 0.00 0 0 0 2 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 0 0 2 Misses in library cache during parse: 0 Optimizer mode: CHOOSE Parsing user id: 89 (ETL) ******************************************************************************** SELECT * FROM SCOTT.EMP call count
cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 1 0.00 0.00 0 7 0 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.00 0.00 0 7 0 14 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: 89 (ETL) Rows Execution Plan ------- --------------------------------------------------- SELECT STATEMENT MODE: CHOOSE TABLE ACCESS MODE: ANALYZED (FULL) OF 'EMP' (TABLE) ******************************************************************************** ALTER SESSION SET
sql_TRACE = FALSE call count
cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 0 0 0 Misses in library cache during parse: 1 Optimizer mode: CHOOSE Parsing user id: 89 (ETL) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count
cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 5 0.00 0.00 0 0 0 0 Execute 5 0.00 0.00 0 0 0 2 Fetch 1 0.00 0.00 0 7 0 14 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 11 0.00 0.00 0 7 0 16 Misses in library cache during parse: 2 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count
cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 0 0.00 0.00 0 0 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 0 0.00 0.00 0 0 0 0 Misses in library cache during parse: 0 user
sql statements in session. internal
sql statements in session.
sql statements in session. statement EXPLAINed in this session. ******************************************************************************** Trace file: D:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\TRACE/wgods_ora_3940.trc Trace file compatibility: 10.01.00 Sort options: default session in tracefile. user
sql statements in trace file. internal
sql statements in trace file.
sql statements in trace file. unique
sql statements in trace file.
sql statements EXPLAINed using schema: ETL.prof$plan_table Default table was used. Table was created. Table was dropped. lines in trace file. elapsed seconds in trace file. 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,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,(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工具中也可以通过它提供的
功能来查看执行顺序。如下图所示： clip_image014 2.执行计划中字段解释 clip_image016
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 clip_image018 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扫描是最快的访问数据方式
原文链接：https://www.f2er.com/oracle/208615.html
