Orace SQL调优系列之执行计划学习笔记

╰+攻爆jí腚メ 2021-09-19 03:10 391阅读 0赞

### 文章目录 ###

*   *  一、获取执行计划的方法
     *   *  (1) explain plan for
         *  (2) set autotrace on
         *  (3) statistics\_level=all
         *  (4) dbms\_xplan.display\_cursor获取
         *  (5) 事件10046 trace跟踪
         *  (6) awrsqrpt.sql
     *  二、解释经典执行计划的方法
     *   *  【单独型】
         *  【联合型关联型】
         *   *  (1) 联合型的关联型(NL)
             *  (2) 联合型的关联型(FILTER)
             *  (3) 联合型的关联型(UPDATE)
             *  (4) 联合型的关联型(CONNECT BY WITH FILTERING)
         *  【联合型非关联型】

最近拜读《收获，不止SQL优化》一书，并做了笔记，方便自己以后回顾，同时放在网上或许也有益于别人

## 一、获取执行计划的方法 ##

### (1) explain plan for ###

步骤：

>  *  1：explain plan for 你的SQL;
>  *  2：select \* from table (dbms\_xplan. display()) ;

*  优点：不需要真的执行，快捷方便
 *  缺点：没有输出运行时的统计信息(逻辑读、递归调用，物理读)，因为没有真正执行，所以不能看到执行了多少行、表被访问了多少次等等

### (2) set autotrace on ###

sqlplus登录：

用户名/密码@主机名称:1521/数据库名

步骤：

>  *  1：set sutoatrace on
>  *  2：在此次执行你的sql;

*  优点：可以看到运行时的统计信息(逻辑读、递归调用，物理读)
 *  缺点：不能看到表被访问了多少次，也需要等sql执行完成才能看

### (3) statistics\_level=all ###

步骤：

>  *  1：alter session set statistics\_level=all;
>  *  2：在此处执行你的SQL;
>  *  3：select \* from table(dbms\_xplan.display\_cursor(null , null,‘allstats last’));

假如使用了Hint语法: /\*+ gather\_plan\_statistics \*/，就可以省略步骤1，直接执行步骤2和3,获取执行计划

关键字解读：

*  Starts：该SQL执行的次数
 *  E-Rows：为执行计划预计的行数
 *  A-Rows：实际返回的行数，E-Rows和A-Rows作比较，就可以看出具体那一步执行计划出问题了
 *  A-Time：每一步实际执行的时间，可以看出耗时的SQL
 *  Buffers：每一步实际执行的逻辑读或一致性读
 *  Reads：物理读
 *  OMem：当前操作完成所有内存工作区操作总使用私有内存工作区(PGA)的大小
 *  lMem：当工作区大小无法符满足操作需求的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass；否则为多次通过，Multi-Pass)。改数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个是由优化器统计数据以及前一次执行的性能数据估算得出的
 *  Used-Mem：语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数，1次即为One-Pass,大于一次则为Mullti-Pass，如果没有使用磁盘，则显示为OPTI1MAL)  
    OMem、lMem为执行所需要的内存评估值，OMem为最优执行模式所需要内存的评估值，Used-Mem为消耗的内存

优点：

*  可以从STARTS得出表被访问多少次；
 *  可以清晰地从E-ROWS和A-ROWS中分别得出预测的行数和真实的行数  
    缺点：
 *  必须等到语句真正执行完成后，才可以得出结果
 *  无法控制记录打屏输出，不想aututrace有traceonly命令
 *  没有专门的输出统计信息，看不到递归调用的次数，看不出物理读具体数值，不过有逻辑读，逻辑读才是重点

### (4) dbms\_xplan.display\_cursor获取 ###

步骤  
从共享池获取

//${SQL_ID}参数可以从共享池拿
    select * from table(dbms_xplan.display_cursor(${SQL_ID}));

还可以从AWR性能视图里获取

select * from table(dbms_xplan.display_awr(${SQL_ID}));

多个执行计划的情况,可以用类似方法查出

select * from table(dbms_xplan.display_cursor(${SQL_ID},0));
    
    select * from table(dbms_xplan.display_cursor(${SQL_ID},1));

优点：

*  和explain一样不需要真正执行，知道sql\_id就好

缺点：

*  不能判断处理了多少行
 *  无法判断表被访问了多少次
 *  没有输出运行时的相关统计信息(逻辑读、递归调用、物理读)

### (5) 事件10046 trace跟踪 ###

步骤：

1：alter session set events '10046 trace name context forever,level 12';//开启跟踪
    2：执行你的语句
    3：alter session set events '10046 trace name context off';//关闭跟踪
    4：找到跟踪产生的文件
    5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela(格式化命令)

优点：

*  可以看出SQL语句对应的等待事件
 *  可以列出sql语句中的函数调用的
 *  可以看出解析事件和执行事件
 *  可以跟踪整个程序包
 *  可以看出处理的行数，产生的逻辑读  
    缺点：
 *  步骤比较繁琐
 *  无法判断表被访问了多少次
 *  执行计划中的谓词部分不能清晰地显示出来

### (6) awrsqrpt.sql ###

步骤：

1：@?/rdbms/admin/awrsqrpt.sql
    具体可以参考我之前的博客：https://smilenicky.blog.csdn.net/article/details/89429989

## 二、解释经典执行计划的方法 ##

可以分为两种类型：单独型和联合型

联合型分为：关联的联合型和非关联的联合型

### 【单独型】 ###

单独型比较好理解，执行顺序是按照id=1,id=2,id=3执行，**由远及近**  
先scott登录，然后执行sql，例子来自《收获，不止SQL优化》一书

select deptno, count(*)
      from emp
     where job = 'CLERK'
       and sal < 3000
     group by deptno

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]  
所以可以给出单独型的图例：  
![在这里插入图片描述][20190427171931924.png]

### 【联合型关联型】 ###

#### (1) 联合型的关联型(NL) ####

这里使用Hint的nl

select /*+ ordered use_nl(dept) index(dept) */ *
      from emp, dept
     where emp.deptno = dept.deptno
       and emp.comm is null
       and dept.dname != 'SALES'

这图来自《收获，不止SQL优化》，可以看出id为2的A-Rows实践返回行数为10，id为3的Starts为10，**说明驱动表emp访问的结果集返回多少条记录，被驱动表就被访问多少次**，这是关联型的显著特征  
![在这里插入图片描述][2019042718180072.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]  
**关联型不一定是驱动表返回多少条，被驱动表就被访问多少次的，注意FILTER模式也是关联型的**

#### (2) 联合型的关联型(FILTER) ####

前面已经介绍了联合型关联型(nl)这种方法的，这种方法是驱动表返回多少条记录，被驱动表就被访问了多少次，不过这种情况对于FILTER模式下并不适用

执行SQL，这里使用Hint /\*+ no\_unnset \*/

select * from emp where not exists (select /*+ no_unnset */ 0 from dept 
    where dept.dname='SALES' and dept.deptno = emp.deptno) and not exists(select /*+ no_unnset */ 0 from bonus where bonus.ename = emp.ename)

ps:图来自《收获，不止SQL优化》一书，这里可以看出id为2的地方，A-Rows实际返回行数为8，而id为3的地方，Starts为3，说明对应SQL执行3次，也即dept被驱动表被访问了3次，这和刚才介绍的nl方式不同，为什么不同?  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]查询一下SQL，可以看出实际返回3条，其它的都是重复多的，

select dname, count(*) from emp, dept where emp.deptno = dept.deptno group by dname;

所以，就很明显了，被过滤了重复数据，也就是说FILTER模式的对数据进行过滤，驱动表执行结果集返回多少行**不重复**数据，被驱动表就被访问多少次，FILTER模式可以说是对nl模式的改善

#### (3) 联合型的关联型(UPDATE) ####

update emp e1 set sal = (select avg(sal) from emp e2 where e2.deptno = e1.deptno),comm = (select avg(comm) from emp e3)

联合型的关联型(UPDATE)和FILTER模式类似，所以就不重复介绍

#### (4) 联合型的关联型(CONNECT BY WITH FILTERING) ####

select /*+ connect_by_filtering */ level, ename ,prior
    ename as manager from emp start with mgr is null  connect by prior empno = mgr

给出联合型关联型图例：  
![在这里插入图片描述][20190427171957297.png]

### 【联合型非关联型】 ###

可以执行SQL

select ename from emp union all select dname from dept union all select '%' from dual

对于plsql可以使用工具查看执行计划，sqlplus客户端的可以使用statistics\_level=all的方法获取执行计划，具体步骤

>  *  1：alter session set statistics\_level=all;
>  *  2：在此处执行你的SQL;
>  *  3：select \* from table(dbms\_xplan.display\_cursor(null , null,‘allstats last’));

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]  
可以给出联合型非关联型的图例：  
![在这里插入图片描述][20190427171947286.png]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210812/9232c1207c2344ccbdabf28386b84d52.png
[20190427171931924.png]: /images/20210812/2d4322c9125447edbcf04d13a58f4830.png
[2019042718180072.png]: /images/20210812/12d00f4c06b6429db89803047e34bc44.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210812/59121c32eb174e8d9fe2f3151aa5a62c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: /images/20210812/d9850d6f0c8242ab95b9da6000e04a32.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]: /images/20210812/e2fe4dac981b4536a6d428200771d0c4.png
[20190427171957297.png]: /images/20210812/1a039f06477b4044a32dba6644858cb4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]: /images/20210812/890a09eb3ca24635a065f6c35ac0e720.png
[20190427171947286.png]: /images/20210812/9588631ca2284046bc5e6299c72865c1.png