ACCESSPLSQL_性能优化系列15_Oracle Explain Plan解析计划解读

2014-12-19 Created By BaoXinjian

ACCESS 1一、摘要


于SQL语句之履行计划中,包含很多字段项和不少模块,其殊字段代表了不同的意义且在不同之状态下一些字段、模块显示或未显,下

面的描述为闹了行计划遭到列字段的含义和各级模块的叙述。

 

**ACCESS 2老二、执行计划分析过程**


  1. 浅析分析计划

Step1. 开拓熟悉的查阅工具:PL/SQL
Developer – Toad。

在PL/SQL
Developer中形容好同一截SQL代码后,按F5,PL/SQL
Developer会自动打开执行计划窗口,显示该SQL的实施计划。

Step2. 查看总COST,获得资源消耗的圆印象

 一般而言,执行计划第一推行所对应的COST(即资本耗费)值,反应了运转就段SQL的整量成本,单圈是总资金没有实际意义,

但可用其同平逻辑不
同履计划之SQL的完全COST进行较,通常COST低的履行计划使好有的。  

Step3.
据自漏洞百出到右,从达成至下之方,了解履行计划的行步骤

推行计划仍层次逐步缩进,从左至右圈,缩进最多的那么无异步,最先执行,如果缩进量相同,则随自上而下的办法判断执行顺序,可概括认为点的步调优先履。

各个一个执行步骤都有相应的COST,可打单步COST的高低,以及单步的估量结果集(对应ROWS/基数),来分析表的看方式,连接各个及总是方式是
否合理。

Step4. 分析表的拜访方式

表明的拜会方式要是零星种:

全表扫描(TABLE ACCESS
FULL)和目录扫描(INDEX
SCAN),如果表上有选择性很好之目,却走了全表扫描,而且是大表的全表扫描,就说明表的走访方式或在问题;

如大表上尚未确切的目录而倒了全表
扫描,就得分析能否成立目录,或者是不是会选再方便的表连接方式和连接各个为提高效率。

Step5. 分析表的总是方式和连接各个

说明的连续各个:就是以哪张表作为驱动表来连续其他表底程序访问顺序。

说明的接连方式:简单来讲,就是少数独说明得满足条件的数码时之连过程。

重点出三栽表连接方式,嵌套循环(NESTED
LOOPS)、哈希连接(HASH JOIN)和排序-合并连接(SORT MERGE
JOIN)。我们经常表现得是嵌套循环和哈希连接。

嵌套循环:

极致适用也是太简易的接连方式。类似于用半重叠循环处理两个游标,外层游标称作驱动表,Oracle检索驱动表的多少,一漫漫一修的代入内层游标,查找满足WHERE条件的具备数据,因此内层游标表中只是用索引的选择性越好,嵌套循环连接的性就逾强。

哈希连接:

先拿驱动表的多寡据规范字段以散列的点子放入内存,然后于内存中相当满足条件的实施。

哈希连接要发适用的内存,而且要于CBO优化模式下,连接两表的WHERE条件有相当号的状态下才足以利用。哈希连接在表的数据量较生,表中并未适当的目可用时比较嵌套循环的频率要大。

 

  1. 小结两碰:

2.1
这里看的施行计划,只是SQL运行前可能的实践措施,实际运作时或许以软硬件环境的例外,而富有改观,而且cost高的实行计划,不自然当实际上运行起来,速度就决然差,我们平常要做实施计划,和事实上测试的运转时刻,来规定一个执计划之优劣。

2.2
对于表底总是各个,多数场面下使用的是嵌套循环,尤其是当目录可用性好之状态下,使用嵌套循环式最为好的,但当ORACLE发现需看的数据表较充分,索引
的本金比高要没适合的目录可用时,会考虑动用哈希连接,以提高效率。排序合并连接的习性最好差,但于在排序需求,或者在非等值连接无法使用哈希连接
的状下,排序合并的频率,也或比哈希连接要嵌套循环要好。

 

ACCESS 3老三、执行计划面临每字段的叙述


  1. 着力字段(总是可用之)

  2. Id           
    执行计划中各个一个操作(行)的标识符。如果数字前面带有星号,意味着将以随后提供这行包含的称词信息

  3. Operation 
    对承诺尽之操作。也为行源操作
  4. Name        操作的靶子名称

  5. 查询优化器评估信息

  6. Rows(E-Rows)    
    预估操作返回的记录条数

  7. Bytes(E-Bytes)   
    预估操作返回的记录字节数
  8. TempSpc           
    预估操作使用临时表空间的尺寸
  9. Cost(%CPU)       
    预估操作所急需的开销。在括号中列有了CPU开销的比例。注意这些价值是透过执行计划计算出来的。换句话说,父操作的付出包含子操作的开发
  10. 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)

  • I/O
    统计(当设定参数statistics_level为all或使用gather_plan_statistics提示时,下列字段可见)

  • Buffers    
    执行中进行的逻辑读操作数量

  • Reads     
    执行中开展的物理读操作数量
  • Writes     
    执行中开展的物理写操作数量         

  • 内存以统计

  • OMem       
    最帅执行所欲内存的预估值

  • 1Mem       
    一破通过(one-pass)执行所急需内存的预估值
  • 0/1/M       
    最优异/一不好经过/多次通过(multipass)模式操作实施的次数
  • Used-Mem 
    最后一糟糕实施时操作以的外存量
  • Used-Tmp 
    最后一不成施行时操作下的现空间尺寸。这个字段必须扩大1024加倍才能够同任何衡量内存的字段一致(比如,32k代表32MB)
  • Max-Tmp  
    操作使用的顶老临时空间尺寸。这个字段必须扩大1024倍增才会及其他衡量内存的字段一致(比如,32k表示32MB)

 

**ACCESS 4季、执行计划着各个模块的叙述和举例**


  1. 实行前,系统预估解析计划,Explain
    Plan

    SQL> explain plan for

    1. 2 select * from emp e,dept d
    2. 3 where e.deptno=d.deptno
    3. 4 and e.ename=’SMITH’;
    4. 06.Explained.
    5. 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语句子之哈希植
    6. 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.—————————————————————————————-
    7. 31.Query Block Name / Object Alias (identified by operation id): –这有显得的为查询块名和目标别名
      32.————————————————————-
    8. 1 – SEL$1 –SEL$为select 的缩写,位于块1,相应的还有DEL$,INS$,UPD$等
    9. 2 – SEL$1 / E@SEL$1 –E@SEL$1,对诺到实践计划中之操作ID为2及,即于表E上之询问,E为别名,下面好像以及
    10. 3 – SEL$1 / D@SEL$1
    11. 4 – SEL$1 / D@SEL$1
    12. 39.Outline Data –提纲部分,这有些将执行计划着的图形化方式为文件形式来呈现,即转换为提醒符方式
      40.————-
    13. /*+
    14. BEGIN_OUTLINE_DATA
    15. USE_NL(@”SEL$1″ “D”@”SEL$1”) –使用USE_NL提示,即嵌套循环
    16. LEADING(@”SEL$1″ “E”@”SEL$1” “D”@”SEL$1”) –指明引导表
    17. INDEX_RS_ASC(@”SEL$1″ “D”@”SEL$1” (“DEPT”.”DEPTNO”)) –指明对于D上的看方式吧用索引
    18. FULL(@”SEL$1″ “E”@”SEL$1”) –指明对E上之访方式为全表扫描
    19. OUTLINE_LEAF(@”SEL$1″)
    20. ALL_ROWS
    21. OPTIMIZER_FEATURES_ENABLE(‘10.2.0.3’)
    22. IGNORE_OPTIM_EMBEDDED_HINTS
    23. END_OUTLINE_DATA
    24. */
    25. 55.Predicate Information (identified by operation id): –谓词信息有,在履行计划遭遇ID带有星号的各国一样履都指向承诺交脚被的一行
      56.—————————————————
    26. 2 – filter(“E”.”ENAME”=’SMITH’)
    27. 4 – access(“E”.”DEPTNO”=”D”.”DEPTNO”)
    28. 61.Column Projection Information (identified by operation id): –执行时每一样步骤所返的排,下面的不比步骤返回了不同之排
      62.———————————————————–
    29. 1 – (#keys=0) “E”.”EMPNO”[NUMBER,22], “E”.”ENAME”[VARCHAR2,10],
    30. “E”.”JOB”[VARCHAR2,9], “E”.”MGR”[NUMBER,22], “E”.”HIREDATE”[DATE,7],
    31. “E”.”SAL”[NUMBER,22], “E”.”COMM”[NUMBER,22], “E”.”DEPTNO”[NUMBER,22],
    32. “D”.”DEPTNO”[NUMBER,22], “D”.”DNAME”[VARCHAR2,14], “D”.”LOC”[VARCHAR2,13]
    33. 2 – “E”.”EMPNO”[NUMBER,22], “E”.”ENAME”[VARCHAR2,10], “E”.”JOB”[VARCHAR2,9],
    34. “E”.”MGR”[NUMBER,22], “E”.”HIREDATE”[DATE,7], “E”.”SAL”[NUMBER,22],
    35. “E”.”COMM”[NUMBER,22], “E”.”DEPTNO”[NUMBER,22]
    36. 3 – “D”.”DEPTNO”[NUMBER,22], “D”.”DNAME”[VARCHAR2,14], “D”.”LOC”[VARCHAR2,13]
    37. 4 – “D”.ROWID[ROWID,10], “D”.”DEPTNO”[NUMBER,22]
    38. 74.Note –注释与叙述有,下面的叙述中被出了此次SQL语句以了动态采样功能
      75.—–
      • dynamic sampling used for this statement
    39. 78.58 rows selected.

 

  1. 履行后,系统实际解析计划,Explain
    Plan

    SQL> select /+ gather_plan_statistics / * –注意此处增加了提示gather_plan_statistics并且该语句被实施

    1. 2 from emp e,dept d
    2. 3 where e.deptno=d.deptno
    3. 4 and e.ename=’SMITH’;
    4. 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS
    5. 08.SQL> select * from table(dbms_xplan.display_cursor(null,null,’iostats last’)); –使用display_cursor获取实际的行计划
    6. 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’
    7. 15.Plan hash value: 351108634 –SQL 语句之哈希值
    8. –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.———————————————————————————————————–
    9. 26.Predicate Information (identified by operation id):
      27.—————————————————
    10. 2 – filter(“E”.”ENAME”=’SMITH’)
    11. 4 – access(“E”.”DEPTNO”=”D”.”DEPTNO”)
    12. 32.Note
      33.—–
      • dynamic sampling used for this statement
    13. 37.26 rows selected.

 

Thank and Regards

转载:乐沙弥 –
http://blog.csdn.net/leshami/article/details/6860007

ACCESS 5

相关文章