执行计划中各个字段的含义描述及获取方法
一、概述
当一条SQL执行很慢时，我们需要分析SQL的执行计划来判断问题的所在。得到一条sql的执行计划有如下三种方法：
1）set autot trace（设置autotrace）
select .... from ....（执行sql语句，得到执行计划）
2）explain plan for selelct .... from ...（解析sql语句，隐式将得到的执行计划写到plan_table表中）
select * from table(dbms_xplan.display());（查看执行计划，实际是利用dbms_xplan.display函数读取的plan_table表的信息）
3）①select ... from ..（执行sql语句）
select * from table(dbms_xplan.display_cursor('sql_id','child_number,format));（查看执行计划）
②在执行sql语句时，加入hint:/*+ gather_plan_statistics*/：select /*+ gather_plan_statistics*/... from ..
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));（查看执行计划）
在这三种获取执行计划的方法中，方法1）、2）显示的信息为估算的执行计划，而并不是真实的执行计划（主要是指显示出来的：消耗时间、返回行数这些指标，而是选择索引还是全表扫这种计划是一致的）。方法2）中oracle并没有执行sql，仅仅是生成估算的执行计划，并将执行计划的信息写到plan_table里，每个执行计划在plan_table表里是一行数据。方法1）虽然执行了sql语句，但是执行计划显示的的内容都是估算的值。
而方法3）中是根据sql实际执行后的sql_id得出真实的执行计划。其实②是①的变形而已，都是使用了dbms_xplan.display_cursor()函数。但是②中使用了hint，这可以使得②得出的执行计划的内容更加详细。
二、实验分析
下面针对同一条语句依次使用三种情况分析（生产环境中的一条sql，有点长）：
2.1使用set autot trace
SQL> set autot trace
SQL> 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 version
Statistics
----------------------------------------------------------
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 version
19 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 DESC
Plan hash value: 328594437
PLAN_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 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
Plan 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
如需转载，请标明出处和链接，谢谢！
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。