执行计划中各个字段的含义描述及获取方法
来源：互联网 发布：网络段子精选哲理 编辑：程序博客网 时间：2024/06/05 02:34
一、概述
当一条SQL执行很慢时，我们需要分析SQL的执行计划来判断问题的所在。得到一条sql的执行计划有如下三种方法：
1）set autot trace（设置autotrace）
select .... from ....（执行sql语句，得到执行计划）
2）dbms_xplan.display函数
explain plan for selelct .... from ...（explain plan for以sql语句为参数做为输入，隐式输出执行计划并写到plan_table表中,plan_table是plan_table$表的public synonym）
select * from table(dbms_xplan.display);（查看执行计划，实际是利用dbms_xplan.display函数读取的plan_table表的信息）
如果在生产环境中一条sql使用了绑定变量，使用该方法查看该sql的执行计划时，我们最好不要用具体值替换绑定变量，否则就是提交向explain plan提交了一条不同的sql，此时可能因为sql profile,存储刚要,sql计划基线的存在对查询优化器的决定产生影响。exilain plan支持有带有绑定变量的sql。
如：有如下存储过程
create or replace procedure p (p_value in number） is
begin
for i in (select * from emp where empno =p_value)
loop
---do something;
end loop;
end;
查看pl/sql中query语句的执行计划：explain plan for select * from emp where empno= :p_value;
在explain plan语句中的绑定变量的使用方法是在变量前加冒号。但是值得注意：explain plan for 语句中的绑定变量不会发生绑定变量窥视，这使得使用了绑定变量的exlain plan得到执行计划和真实的计划可能并不相同。
3）dbms_xplan.display_cursor()函数
该函数从库缓存中读取实际的执行计划，也就是从视图v$sql,v$session,v$sql_plan,v$sql_plan_statistics_all,v$sql_plan_statistics。
①select ... from ..（执行sql语句）
select * from table(dbms_xplan.display_cursor('sql_id','child_number,format));（查看执行计划，执行计划是真实的，但是成本消耗信息是预估的，并不是运行时的成本消耗）
该方法调用了v$sql_plan和v$sql_plan_statistics_all视图中记录的执行计划的信息进行展示。
②在执行sql语句时，加入hint:/*+ gather_plan_statistics*/：select /*+ gather_plan_statistics*/... from ..
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));（查看执行计划，使用hint后，会收集运行时的成本消耗，因为收集运行时的成本需要消耗较多的资源，所以其它方式均不收集运行时的统计信息）
其实②是①的变形而已，都是使用了dbms_xplan.display_cursor()函数。但是②中使用了hint，这可以使得②得出的执行计划的内容更加详细。
4）dbms_xplan.display_awr()函数
select * from table(dbms_xplan.display_awr('sql_id',null,null,'all');
当采集快照的时候，awr就能够收集执行计划。存储执行计划的资料库和v$sql_plan视图基本相同，为dba_hist_sql_plan。通过dbms_xplan.display_awr()函数可以查询视图中的信息以图表的形式展现执行计划。
5）使用10046事件
Note：方法1）2）得到的执行计划并不是实际执行时的计划，尽管大多时候它和实际执行时使用的计划一致的，但偶有不一致的情况，3）4）5）显示的执行计划都是实际执行时使用的计划。但在方法1）-5）中，除了3）中的②之外，显示的成本消耗值均为预估值，数据库默认不会收集sql运行时的成本消耗，因为这中收集本身会带来很大的资源消耗，只有使用3)中②方法或是设置statistics_level=all时会收集，并写入v$sql_plan_statistics视图。
二、实验分析
下面针对同一条语句依次使用三种情况分析（生产环境中的一条sql，有点长）：
2.1使用set autot trace
SQL> set autot traceSQL> SELECT C.RPT_ID, 2 MAX(C.NAME), 3 C.STAT_DATE, 4 SUM(A.RCV_AMT), 5 SUM(B.THIS_PENALTY), 6 SUM(B.PREPAY_AMT), 7 C.TYPE_CODE 8 FROM sgpms.A_PAY_FLOW A, sgpms.PUB_A_PAY_RELA B, sgpms.A_BUSI_RPT C 9 WHERE A.CHARGE_ID = B.CHARGE_ID 10 AND B.RPT_ID = C.RPT_ID 11 AND C.TYPE_CODE = '08' 12 AND C.STAT_EMP_NO = '00040532' 13 AND C.STAT_DATE >= '20110101' 14 AND C.STAT_DATE <= '20140226' 15 GROUP BY C.RPT_ID, C.STAT_DATE, C.TYPE_CODE 16 ORDER BY C.RPT_ID DESC;647 rows selected.Execution Plan----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2152K| 188M| | 471K (3)| | || 1 | SORT GROUP BY | | 2152K| 188M| 3230M| 471K (3)| | || 2 | HASH JOIN | | 31M| 2720M| | 308K (4)| | || 3 | TABLE ACCESS BY INDEX ROWID| A_BUSI_RPT | 3813 | 189K| | 988 (1)| | || 4 | INDEX RANGE SCAN | IND_A_BUSI_RPT_005 | 5324 | | | 5 (0)| | || 5 | HASH JOIN | | 60M| 2369M| 1439M| 306K (3)| | || 6 | PARTITION RANGE ALL | | 60M| 748M| | 175K (3)| 1 | 8 || 7 | PARTITION LIST ALL | | 60M| 748M| | 175K (3)| 1 | LAST || 8 | TABLE ACCESS FULL | A_PAY_FLOW | 60M| 748M| | 175K (3)| 1 | 880 || 9 | TABLE ACCESS FULL | PUB_A_PAY_RELA | 60M| 1609M| | 69324 (5)| | |-----------------------------------------------------------------------------------------------------------------Note----- - 'PLAN_TABLE' is old versionStatistics---------------------------------------------------------- 3001 recursive calls 0 db block gets 2626565 consistent gets 371318 physical reads 3772 redo size 26485 bytes sent via SQL*Net to client 965 bytes received via SQL*Net from client 45 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 647 rows processed在执行计划表中rows,bytes,TempSpc,Cost都是预估算的值。实际执行时，这些值会与真实值存在差入。差入可能很大，也可能很小。（每个字段的代表的含义后面会有讲解）2.2 使用explain plan for...
SQL> explain plan for 2 SELECT C.RPT_ID, 3 MAX(C.NAME), 4 C.STAT_DATE, 5 SUM(A.RCV_AMT), 6 SUM(B.THIS_PENALTY), 7 SUM(B.PREPAY_AMT), 8 C.TYPE_CODE 9 FROM sgpms.A_PAY_FLOW A, sgpms.PUB_A_PAY_RELA B, sgpms.A_BUSI_RPT C 10 WHERE A.CHARGE_ID = B.CHARGE_ID 11 AND B.RPT_ID = C.RPT_ID 12 AND C.TYPE_CODE = '08' 13 AND C.STAT_EMP_NO = '00040532' 14 AND C.STAT_DATE >= '20110101' 15 AND C.STAT_DATE <= '20140226' 16 GROUP BY C.RPT_ID, C.STAT_DATE, C.TYPE_CODE 17 ORDER BY C.RPT_ID DESC;Explained.SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2152K| 188M| | 471K (3)| | || 1 | SORT GROUP BY | | 2152K| 188M| 3230M| 471K (3)| | || 2 | HASH JOIN | | 31M| 2720M| | 308K (4)| | || 3 | TABLE ACCESS BY INDEX ROWID| A_BUSI_RPT | 3813 | 189K| | 988 (1)| | || 4 | INDEX RANGE SCAN | IND_A_BUSI_RPT_005 | 5324 | | | 5 (0)| | || 5 | HASH JOIN | | 60M| 2369M| 1439M| 306K (3)| | || 6 | PARTITION RANGE ALL | | 60M| 748M| | 175K (3)| 1 | 8 |PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------| 7 | PARTITION LIST ALL | | 60M| 748M| | 175K (3)| 1 | LAST || 8 | TABLE ACCESS FULL | A_PAY_FLOW | 60M| 748M| | 175K (3)| 1 | 880 || 9 | TABLE ACCESS FULL | PUB_A_PAY_RELA | 60M| 1609M| | 69324 (5)| | |-----------------------------------------------------------------------------------------------------------------Note----- - 'PLAN_TABLE' is old version19 rows selected.执行计划表中的Rows--Pstop列的值同样为估算值
2.3 使用hint和dbms_xplan.display_cursor
SELECT /*+ gather_plan_statistics */ C.RPT_ID, MAX(C.NAME), C.STAT_DATE, SUM(B.THIS_PENALTY), SUM(B.PREPAY_AMT), C.TYPE_CODE FROM sgpms.PUB_A_PAY_RELA B, sgpms.A_BUSI_RPT C WHERE B.RPT_ID = C.RPT_ID AND C.TYPE_CODE = '08' AND C.STAT_EMP_NO = '00040532' AND C.STAT_DATE >= '20110101' AND C.STAT_DATE <= '20140226' GROUP BY C.RPT_ID, C.STAT_DATE, C.TYPE_CODE ORDER BY C.RPT_ID DESC select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); PLAN_TABLE_OUTPUT---------------------------------------------------------------------------------------------------------------------------SQL_ID b7n4qkvmx1np8, child number 0-------------------------------------SELECT /*+ gather_plan_statistics */ C.RPT_ID, MAX(C.NAME), C.STAT_DATE, SUM(A.RCV_AMT), SUM(B.THIS_PENALTY),SUM(B.PREPAY_AMT), C.TYPE_CODE FROM sgpms.A_PAY_FLOW A, sgpms.PUB_A_PAY_RELA B, sgpms.A_BUSI_RPT C WHERE A.CHARGE_ID = B.CHARGE_ID AND B.RPT_ID= C.RPT_ID AND C.TYPE_CODE = '08' AND C.STAT_EMP_NO = '00040532' AND C.STAT_DATE >= '20110101' AND C.STAT_DATE <= '20140226' GROUP BY C.RPT_ID,C.STAT_DATE, C.TYPE_CODE ORDER BY C.RPT_ID DESCPlan hash value: 328594437PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | Used-Tmp|-------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 647 |00:02:40.80 | 2626K| 371K| 371K| | | | || 1 | SORT GROUP BY | | 1 | 2152K| 647 |00:02:40.80 | 2626K| 371K| 371K| 160K| 160K| 142K (0)| ||* 2 | HASH JOIN | | 1 | 31M| 2682 |00:02:37.11 | 2626K| 371K| 371K| 865K| 865K| 1248K (0)| ||* 3 | TABLE ACCESS BY INDEX ROWID| A_BUSI_RPT | 1 | 3813 | 1128 |00:00:00.01 | 1261 | 33 | 0 | | | | ||* 4 | INDEX RANGE SCAN | IND_A_BUSI_RPT_005 | 1 | 5324 | 1689 |00:00:00.01 | 9 | 0 | 0 | | | | ||* 5 | HASH JOIN | | 1 | 60M| 60M|00:02:04.45 | 2625K| 371K| 371K| 2061M| 47M| 870M (1)| 2999K|| 6 | PARTITION RANGE ALL | | 1 | 60M| 60M|00:01:00.42 | 1897K| 0 | 0 | | | | || 7 | PARTITION LIST ALL | | 8 | 60M| 60M|00:00:00.26 | 1897K| 0 | 0 | | | | || 8 | TABLE ACCESS FULL | A_PAY_FLOW | 880 | 60M| 60M|00:00:00.91 | 1897K| 0 | 0 | | | | || 9 | TABLE ACCESS FULL | PUB_A_PAY_RELA | 1 | 60M| 60M|00:00:00.01 | 728K| 0 | 0 | | | | |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("B"."RPT_ID"="C"."RPT_ID") 3 - filter(("C"."STAT_DATE">='20110101' AND "C"."STAT_DATE"<='20140226')) 4 - access("C"."STAT_EMP_NO"='00040532' AND "C"."TYPE_CODE"='08') 5 - access("A"."CHARGE_ID"="B"."CHARGE_ID")32 rows selected.我们看到3）→②的方法中，执行计划表中多出了很多列。其中A-开头的列为实际真实的数据，E-开头的字段为预估值。本条SQL中估算的行数和真实的行数有很大差距，这会导致CBO选择错误的执行计划。从A-Time列(真实的执行时间)可以看到执行这条sql花费了较长的时间，这就是因为CBO利用统计信息进行了误差较大的估算，导致选用了错误的执行计划。在Operation字段，可以看到执行计划主要使用了hash join进行多表连接。为了优化这条sql的执行时间，我根据这条SQL涉及到表的特征，利用hint让执行计划使用nested loop（嵌套循环）连接。
SELECT /*+ gather_plan_statistics use_nl(C B A)*/ C.RPT_ID, MAX(C.NAME), C.STAT_DATE, SUM(A.RCV_AMT), SUM(B.THIS_PENALTY), SUM(B.PREPAY_AMT), C.TYPE_CODE FROM sgpms.A_PAY_FLOW A, sgpms.PUB_A_PAY_RELA B, sgpms.A_BUSI_RPT C WHERE A.CHARGE_ID = B.CHARGE_ID AND B.RPT_ID = C.RPT_ID AND C.TYPE_CODE = '08' AND C.STAT_EMP_NO = '00040532' AND C.STAT_DATE >= '20110101' AND C.STAT_DATE <= '20140226' GROUP BY C.RPT_ID, C.STAT_DATE, C.TYPE_CODE ORDER BY C.RPT_ID DESC SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------SQL_ID bd63ph7uazaq6, child number 0-------------------------------------SELECT /*+ gather_plan_statistics use_nl(C B A)*/ C.RPT_ID, MAX(C.NAME), C.STAT_DATE, SUM(A.RCV_AMT),SUM(B.THIS_PENALTY), SUM(B.PREPAY_AMT), C.TYPE_CODE FROM sgpms.A_PAY_FLOW A, sgpms.PUB_A_PAY_RELA B, sgpms.A_BUSI_RPT CWHERE A.CHARGE_ID = B.CHARGE_ID AND B.RPT_ID = C.RPT_ID AND C.TYPE_CODE = '08' AND C.STAT_EMP_NO = '00040532' AND C.STAT_DATE>= '20110101' AND C.STAT_DATE <= '20140226' GROUP BY C.RPT_ID, C.STAT_DATE, C.TYPE_CODE ORDER BY C.RPT_ID DESCPlan hash value: 2963661606------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 647 |00:00:00.09 | 17903 | | | || 1 | SORT GROUP BY | | 1 | 2152K| 647 |00:00:00.09 | 17903 | 133K| 133K| 118K (0)|| 2 | NESTED LOOPS | | 1 | 31M| 2682 |00:00:00.13 | 17903 | | | || 3 | NESTED LOOPS | | 1 | 30M| 2684 |00:00:00.03 | 7167 | | | ||* 4 | TABLE ACCESS BY INDEX ROWID | A_BUSI_RPT | 1 | 3813 | 1128 |00:00:00.01 | 1261 | | | ||* 5 | INDEX RANGE SCAN | IND_A_BUSI_RPT_005 | 1 | 5324 | 1689 |00:00:00.01 | 9 | | | || 6 | TABLE ACCESS BY INDEX ROWID | PUB_A_PAY_RELA | 1128 | 8088 | 2684 |00:00:00.04 | 5906 | | | ||* 7 | INDEX RANGE SCAN | IND_PUB_PAY_RELA_RPT | 1128 | 8405 | 2684 |00:00:00.01 | 3401 | | | || 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| A_PAY_FLOW | 2684 | 1 | 2682 |00:00:00.04 | 10736 | | | ||* 9 | INDEX UNIQUE SCAN | PK_A_PAY_FLOW | 2684 | 1 | 2682 |00:00:00.03 | 8054 | | | |PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 4 - filter(("C"."STAT_DATE">='20110101' AND "C"."STAT_DATE"<='20140226')) 5 - access("C"."STAT_EMP_NO"='00040532' AND "C"."TYPE_CODE"='08') 7 - access("B"."RPT_ID"="C"."RPT_ID") 9 - access("A"."CHARGE_ID"="B"."CHARGE_ID")32 rows selected.通过A-Time列可以看到sql的执行时间已经变短了。
三、执行计划表中，各字段的含义
3.1 基本字段(总是可用的)
Id 执行计划中每一个操作(行)的标识符。如果数字前面带有星号，意味着将在随后提供这行包含的谓词信息
Operation 对应执行的操作。也叫行源操作
Name 操作的对象名称
3.2 查询优化器评估信息
Rows(E-Rows) 预估操作返回的记录条数
Bytes(E-Bytes) 预估操作返回的记录字节数
TempSpc 预估操作使用临时表空间的大小
Cost(%CPU) 预估操作所需的开销。在括号中列出了CPU开销的百分比。注意这些值是通过执行计划计算出来的。
换句话说，父操作的开销包含子操作的开销
Time 预估执行操作所需要的时间(HH:MM:SS)
3.3 分区(仅当访问分区表时下列字段可见)
Pstart 访问的第一个分区。如果解析时不知道是哪个分区就设为KEY,KEY(I),KEY(MC),KEY(OR),KEY(SQ)
Pstop 访问的最后一个分区。如果解析时不知道是哪个分区就设为KEY,KEY(I),KEY(MC),KEY(OR),KEY(SQ)
3.4 并行和分布式处理(仅当使用并行或分布式操作时下列字段可见)
Inst 在分布式操作中，指操作使用的数据库链接的名字
TQ 在并行操作中，用于从属线程间通信的表队列
IN-OUT 并行或分布式操作间的关系
PQ Distrib 在并行操作中，生产者为发送数据给消费者进行的分配
3.5 运行时统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时，下列字段可见)
Starts 指定操作执行的次数
A-Rows 操作返回的真实记录数
A-Time 操作执行的真实时间(HH:MM:SS.FF)
3.6 I/O 统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时，下列字段可见)
Buffers 执行期间进行的逻辑读操作数量
Reads 执行期间进行的物理读操作数量
Writes 执行期间进行的物理写操作数量
3.7 内存使用统计
OMem 最优执行所需内存的预估值
1Mem 一次通过(one-pass)执行所需内存的预估值
0/1/M 最优/一次通过/多次通过(multipass)模式操作执行的次数
Used-Mem 最后一次执行时操作使用的内存量
Used-Tmp 最后一次执行时操作使用的临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致(比如，32k意味着32MB)
Max-Tmp 操作使用的最大临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致(比如，32k意味着32MB)
各个值可以对照二、中的实验进行对照学习。
ballontt
2014/02/27
---The End---
微博：weibo.com/ballontt
如需转载，请标明出处和链接，谢谢！
0 0
- 执行计划中各个字段的含义描述及获取方法
- 执行计划中各个字段的含义描述及获取方法
- oracle执行计划中各字段的描述
- [Oracle]获取执行计划的各个方法总结
- 执行计划中各字段各模块描述
- 执行计划中各字段各模块描述
- 执行计划中各字段各模块描述
- 执行计划中各字段各模块描述
- SQL优化【基础01】-生成执行计划及计划中参数列的含义
- 获取执行计划的方法
- HTTP请求报头中各个字段的含义
- ip结构体中各个字段含义
- Oracle获取执行计划的方法
- 获取执行计划的几种方法
- 获取执行计划的六种方法
- oracle获取执行计划的方法
- c#获取实例的各个字段名称及值
- explain plan for 执行计划中各字段各模块描述
- Media Foundation学习笔记（六）Media Foundation的架构 Media Session
- DOM加载——缓解图片，影音加载慢影响内容显示
- CMake find_package for OpenCV
- 控制输出精度
- 『算法学习笔记』11th day. 文件操作<2>
- 执行计划中各个字段的含义描述及获取方法
- 线程同步
- Mysql 多个子查询 多个LEFT JOIN 视图创建
- 数据结构 -- 二叉树中序遍历
- tomcat加载web.xml
- HDU 1271
- 创业型团队需要的云计算
- 有趣的linux命令
- POJ - Fibonacci 【快速幂 + 矩阵】
