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 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
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: 已处理⾏数
python replace 正则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_ h:\utputfile 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 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
查询结果为wgods_⽂件，但是去相应⽬录却没有到对应的追踪⽂件，⽽是如下trace⽂件：wgods_ora_
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          　递归调⽤
inner join outer join区别
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字节的数据。mysql语句的执行顺序
参考⽂档：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语句产⽣的执⾏计划放在SHA
RE POOL⾥⾯，第⼀次要经过硬解析，产⽣hash值。下次再执⾏时⽐较hash值，如果相同就不会执⾏硬解析。
2> COST
COST没有单位，是⼀个相对值，是SQL以CBO⽅式解析执⾏计划时，供ORACLE来评估CBO成本，选择执⾏计划⽤的。没有明确的含义，但是在对⽐是就⾮常有⽤。
公式：COST=（Single Block I/O COST + MultiBlock I/O Cost + CPU Cost）/ Sreadtim
stl文件下载网站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、动态分析

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。