程序博客网 > 炉石数据库

explain plan for 执行计划中各字段各模块描述

来源：互联网 发布：炉石数据库 编辑：程序博客网 时间：2024/05/21 11:14


一、执行计划中各字段的描述
  1、基本字段(总是可用的)
        Id                 执行计划中每一个操作(行)的标识符。如果数字前面带有星号，意味着将在随后提供这行包含的谓词信息
        Operation  对应执行的操作。也叫行源操作
        Name        操作的对象名称
  
  2、查询优化器评估信息
        Rows(E-Rows)     预估操作返回的记录条数
        Bytes(E-Bytes)      预估操作返回的记录字节数
        TempSpc               预估操作使用临时表空间的大小
        Cost(%CPU)         预估操作所需的开销。在括号中列出了CPU开销的百分比。注意这些值是通过执行计划计算出来的。
                    换句话说，父操作的开销包含子操作的开销
        Time                       预估执行操作所需要的时间(HH:MM:SS)
  
  3、分区(仅当访问分区表时下列字段可见)
    Pstart        访问的第一个分区。如果解析时不知道是哪个分区就设为KEY,KEY(I),KEY(MC),KEY(OR),KEY(SQ) 
    Pstop        访问的最后一个分区。如果解析时不知道是哪个分区就设为KEY,KEY(I),KEY(MC),KEY(OR),KEY(SQ)
  
  4、并行和分布式处理(仅当使用并行或分布式操作时下列字段可见)
        Inst        在分布式操作中，指操作使用的数据库链接的名字
        TQ         在并行操作中，用于从属线程间通信的表队列
        IN-OUT          并行或分布式操作间的关系
        PQ Distrib     在并行操作中，生产者为发送数据给消费者进行的分配
  
  5、运行时统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时，下列字段可见)
        Starts         指定操作执行的次数
        A-Rows     操作返回的真实记录数
        A-Time      操作执行的真实时间(HH:MM:SS.FF)
  
  6、I/O 统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时，下列字段可见)
        Buffers     执行期间进行的逻辑读操作数量
        Reads      执行期间进行的物理读操作数量
        Writes      执行期间进行的物理写操作数量         
  
  7、内存使用统计
        OMem        最优执行所需内存的预估值
        1Mem         一次通过(one-pass)执行所需内存的预估值
        0/1/M          最优/一次通过/多次通过(multipass)模式操作执行的次数
        Used-Mem       最后一次执行时操作使用的内存量
        Used-Tmp        最后一次执行时操作使用的临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致(比如，32k意味着32MB)
        Max-Tmp           操作使用的最大临时空间大小。这个字段必须扩大1024倍才能和其他衡量内存的字段一致(比如，32k意味着32MB)

二、执行计划中各模块的描述与举例
  1、预估的执行计划中的各字段与模块 

[sql] view plaincopyprint?
  1. SQL> explain plan for                                                                                                    
  2.   2  select * from emp e,dept d                                                                                          
  3.   3  where e.deptno=d.deptno                                                                                             
  4.   4  and e.ename='SMITH';                                                                                                
  5.                                                                                                                          
  6. Explained.                                                                                                               
  7.                                                                                                                          
  8. /**************************************************/                                                                     
  9. /* Author: Robinson Cheng                         */                                                                     
  10. /* Blog:   http://blog.csdn.net/robinson_0612     */                                                                     
  11. /* MSN:    robinson_0612@hotmail.com              */                                                                     
  12. /* QQ:     645746311                              */                                                                     
  13. /**************************************************/                                                                     
  14.                                                                                                                          
  15.                                                                                                                          
  16. SQL> set linesize 180                                                                                                    
  17. SQL> set pagesize 0                                                                                                      
  18. SQL> select * from table(dbms_xplan.display(null,null,'advanced'));   --使用dbms_xplan.display函数获得语句的执行计划     
  19. Plan hash value: 351108634                                            --SQL语句的哈希植                                  
  20.                                                                                                                          
  21. ----------------------------------------------------------------------------------------   /*执行计划部分*/              
  22. | Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                 
  23. ----------------------------------------------------------------------------------------                                 
  24. |   0 | SELECT STATEMENT             |         |     1 |   117 |     4   (0)| 00:00:01 |                                 
  25. |   1 |  NESTED LOOPS                |         |     1 |   117 |     4   (0)| 00:00:01 |                                 
  26. |*  2 |   TABLE ACCESS FULL          | EMP     |     1 |    87 |     3   (0)| 00:00:01 |                                 
  27. |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    30 |     1   (0)| 00:00:01 |                                 
  28. |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |                                 
  29. ----------------------------------------------------------------------------------------                                 
  30.                                                                                                                          
  31. Query Block Name / Object Alias (identified by operation id):  --这部分显示的为查询块名和对象别名                        
  32. -------------------------------------------------------------                                                            
  33.                                                                                                                          
  34.    1 - SEL$1                    --SEL$为select 的缩写，位于块1,相应的还有DEL$,INS$,UPD$等                                
  35.    2 - SEL$1 / E@SEL$1          --E@SEL$1，对应到执行计划中的操作ID为2上，即在表E上的查询，E为别名，下面类同             
  36.    3 - SEL$1 / D@SEL$1                                                                                                   
  37.    4 - SEL$1 / D@SEL$1                                                                                                   
  38.                                                                                                                          
  39. Outline Data                    --提纲部分，这部分将执行计划中的图形化方式以文本形式来呈现，即转换为提示符方式           
  40. -------------                                                                                                            
  41.                                                                                                                          
  42.   /*+                                                                                                                    
  43.       BEGIN_OUTLINE_DATA                                                                                                 
  44.       USE_NL(@"SEL$1" "D"@"SEL$1")                           --使用USE_NL提示，即嵌套循环                                
  45.       LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")              --指明前导表                                                
  46.       INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))   --指明对于D上的访问方式为使用索引                           
  47.       FULL(@"SEL$1" "E"@"SEL$1")                             --指明对于E上的访问方式为全表扫描                           
  48.       OUTLINE_LEAF(@"SEL$1")                                                                                             
  49.       ALL_ROWS                                                                                                           
  50.       OPTIMIZER_FEATURES_ENABLE('10.2.0.3')                                                                              
  51.       IGNORE_OPTIM_EMBEDDED_HINTS                                                                                        
  52.       END_OUTLINE_DATA                                                                                                   
  53.   */                                                                                                                     
  54.                                                                                                                          
  55. Predicate Information (identified by operation id): --谓词信息部分，在执行计划中ID带有星号的每一行均对应到下面中的一行   
  56. ---------------------------------------------------                                                                      
  57.                                                                                                                          
  58.    2 - filter("E"."ENAME"='SMITH')                                                                                       
  59.    4 - access("E"."DEPTNO"="D"."DEPTNO")                                                                                 
  60.                                                                                                                          
  61. Column Projection Information (identified by operation id):  --执行时每一步骤所返回的列，下面的不同步骤返回了不同的列    
  62. -----------------------------------------------------------                                                              
  63.                                                                                                                          
  64.    1 - (#keys=0) "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10],                                                       
  65.        "E"."JOB"[VARCHAR2,9], "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7],                                              
  66.        "E"."SAL"[NUMBER,22], "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22],                                             
  67.        "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]                                         
  68.    2 - "E"."EMPNO"[NUMBER,22], "E"."ENAME"[VARCHAR2,10], "E"."JOB"[VARCHAR2,9],                                          
  69.        "E"."MGR"[NUMBER,22], "E"."HIREDATE"[DATE,7], "E"."SAL"[NUMBER,22],                                               
  70.        "E"."COMM"[NUMBER,22], "E"."DEPTNO"[NUMBER,22]                                                                    
  71.    3 - "D"."DEPTNO"[NUMBER,22], "D"."DNAME"[VARCHAR2,14], "D"."LOC"[VARCHAR2,13]                                         
  72.    4 - "D".ROWID[ROWID,10], "D"."DEPTNO"[NUMBER,22]                                                                      
  73.                                                                                                                          
  74. Note    --注释与描述部分，下面的描述中给出了本次SQL语句使用了动态采样功能                                                
  75. -----                                                                                                                    
  76.    - dynamic sampling used for this statement                                                                            
  77.                                                                                                                          
  78. 58 rows selected.                                                                                                        

  2、实际执行计划中的各字段与模块    

[sql] view plaincopyprint?
  1. SQL> select /*+ gather_plan_statistics */ *          --注意此处增加了提示gather_plan_statistics并且该语句被执行          
  2.   2  from emp e,dept d                                                                                                   
  3.   3  where e.deptno=d.deptno                                                                                             
  4.   4  and e.ename='SMITH';                                                                                                
  5.                                                                                                                          
  6.       7369 SMITH      CLERK           7902 17-DEC-80        800                    20         20 RESEARCH       DALLAS   
  7.                                                                                                                          
  8. SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); --使用display_cursor获取实际的执行计划    
  9.                                                                                                                          
  10. SQL_ID  fpx7zw59f405d, child number 0              --这部分给出了SQL语句的SQL_ID,子游标号以及原始的SQL语句               
  11. -------------------------------------                                                                                    
  12. select /*+ gather_plan_statistics */ * from emp e,dept d where e.deptno=d.deptno and                                     
  13. e.ename='SMITH'                                                                                                          
  14.                                                                                                                          
  15. Plan hash value: 351108634              --SQL 语句的哈希值                                                               
  16.                                         --SQL语句的执行计划，可以看到下面显示的字段一部分不同于预估执行计划中的字段      
  17. -----------------------------------------------------------------------------------------------------------              
  18. | Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |              
  19. -----------------------------------------------------------------------------------------------------------              
  20. |   1 |  NESTED LOOPS                |         |      1 |      1 |      1 |00:00:00.01 |      10 |      1 |              
  21. |*  2 |   TABLE ACCESS FULL          | EMP     |      1 |      1 |      1 |00:00:00.01 |       8 |      0 |              
  22. |   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |      1 |00:00:00.01 |       2 |      1 |              
  23. |*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |      1 |      1 |      1 |00:00:00.01 |       1 |      1 |              
  24. -----------------------------------------------------------------------------------------------------------              
  25.                                                                                                                          
  26. Predicate Information (identified by operation id):                                                                      
  27. ---------------------------------------------------                                                                      
  28.                                                                                                                          
  29.    2 - filter("E"."ENAME"='SMITH')                                                                                       
  30.    4 - access("E"."DEPTNO"="D"."DEPTNO")                                                                                 
  31.                                                                                                                          
  32. Note                                                                                                                     
  33. -----                                                                                                                    
  34.    - dynamic sampling used for this statement                                                                            
  35.                                                                                                                          
  36.                                                                                                                          
  37. 26 rows selected.                                                                                                        

三、总结
      由上可知，在不同的情形下可以获得执行计划的不同信息，而不同信息则展现了SQL语句对应的不同情况，因此应根据具体的情形具体分析。      



from：http://blog.csdn.net/robinson_0612/article/details/6860007




炉石数据库 炉石数据库
原创粉丝点击
热门IT博客
珠海长隆 超级激流数据珠海长隆 超级激流数据
java message类java message类
公交线路优化
淘宝iphone6s防水标志
天津淘宝礼仪公司
随机数生成器界面java
成都北方钓鱼台 知乎
网络科学导论讲义
ios访问mac文件
java文本框赋值
淘宝网首页货到付款
试点网络学校基础
女士双肩休闲包 知乎
mysql建表语句详解
高质量产品发布和优化
全国网络通缉名单2017
周易软件研发
淘宝网聚划算网站
饮用水质检测标准数据
网站域名后缀
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 汪氏蜂蜜专卖店 汪氏蜂王浆专卖店 汪氏洋槐蜜 汪氏洋槐蜜怎么样 汪氏蜂王浆价格 汪氏椴树蜜 汪氏蜂王浆 汪氏蜂王浆价格表 汪氏蜜蜂园有限公司 汪氏蜂产品 汪氏蜂王浆怎么吃 汪氏野玫瑰蜂蜜 汪氏蜜蜂园怎么样 汪氏家谱 汪氏蜂王浆液 汪氏枇杷蜂蜜 汪氏的由来 汪氏百花蜜 汪氏蜂蜜产地 汪氏蜂胶价格 汪氏蜂胶的价格 汪氏宗亲网论坛 汪氏蜂胶曝光 汪汪 汪汪图片 小狗汪汪 汪汪对 水汪汪 小汪汪 汪汪汪 蓝汪汪 母岳娘水汪汪 汪汪立功大队 身穿绿衣裳肚里水汪汪儿多 高沟汪小八老婆顾依婷 汪浩 汪海林 汪海 美味汪海 汪海集团 汪海林批陈情令

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里