SQL优化-第二章-从解释计划层面让SQL飞 - codec style - 开源中国
前言
在第一章，我们谈到加强数据库的设计层面认知可以让SQL的跑得更快，这章我们就谈论下如何从语言层面来提供优化SQL。如果说有一把钥匙能打开SQL优化的大门，那么解释计划就是这样的一把钥匙。什么是解释计划--数据根据统计信息生成的代码执行计划，了解释计划，理解解释计划就能帮助我们理解代码的瓶颈，问题的所在，有的放矢进行优化。Oracle提供了多种解释计划，hive，mongodb，mysql等数据库都提供相似的工具。在第一章的时候，举例子的时候也用到解释计划，刚好借这个机会也来说说解释计划。
如何获取解释计划
方式一：explain plan for
用法）
步骤1：explain plan for "你的SQL"
步骤2：select * from table(dbms_xplan.display());
例子）
explain plan for
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19);
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8138 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=18 OR "T1"."N"=19)
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
总结）
优点： 1. 无需真正执行，快捷方便
缺陷： 1. 没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；2.无法判断是处理了多少行；3.无法判断表被访问了多少次。
这只是根据之前的统计信息作出的预判，无法获取真实的逻辑读等信息。还真是得拿数据说话，哈哈
方式二-set autotrace on
用法）
步骤1：set autotrace 【command】
set autotrace on(得到执行计划，输出运行结果）
set autotrace traceonly(得到执行计划，不输出运行结果）
set autotrace traceonly explain (得到执行计划，不输出运行结果和统计信息部分，仅展现执行计划部分）
set autotrace traceonl statistics(不输出运行结果和执行计划部分，仅展现统计信息部分）
步骤2：在此处执行你的SQL即可，后续自然会有结果输出
例子）
set autotrace on
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19);
执行计划
----------------------------------------------------------
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 8138 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."N"=18 OR "T1"."N"=19)
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
1032 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
总结）
优点：
1.可以输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）
2.虽然必须要等语句执行完毕后才可以输出执行计划，但是可以有traceonly开关来控制返回结果不打屏输出。
缺陷：
1.必须要等到语句真正执行完毕后，才可以出结果；
2.无法看到表被访问了多少次。
方式三-statistics_level=all;
用法）
步骤1：alter session set statistics_level=all ; 步骤2：在此处执行你的SQL 步骤3：select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
例子）
set autotrace off
alter session set statistics_level=all ;
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID 1a914ws3ggfsn, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
Plan hash value: 3532430033
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | | 2 |00:00:00.01 | 12 |
| 2 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.01 | 10 |
| 3 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 5 |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 2 | 2 |00:00:00.01 | 5 |
|* 5 | INDEX RANGE SCAN | T1_N | 2 | 1 | 2 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 2 | 1 | 2 |00:00:00.01 | 5 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 2 | 1 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("T1"."N"=18 OR "T1"."N"=19))
6 - access("T1"."ID"="T2"."T1_ID")
Note
-----
- dynamic sampling used for this statement (level=2)
关键字解读）
1.Starts为该sql执行的次数。
2.E-Rows为执行计划预计的行数。
3.A-Rows为实际返回的行数。A-Rows跟E-Rows做比较，就可以确定哪一步执行计划出了问题。
4.A-Time为每一步实际执行的时间（HH：MM：SS.FF），根据这一行可以知道该sql耗时在了哪个地方。
5.Buffers为每一步实际执行的逻辑读或一致性读。
6.Reads为物理读。
7.OMem:当前操作完成所有内存工作区(Work Aera)操作所总共使用私有内存(PGA)中工作区的大小，这个数据是由优化器统计数据以及前一次执行的性能数据估算得出的
8.1Mem:当工作区大小无法满足操作所需的大小时，需要将部分数据写入临时磁盘空间中(如果仅需要写入一次就可以完成操作，就称一次通过，One-Pass;否则为多次通过，Multi_Pass).该列数据为语句最后一次执行中，单次写磁盘所需要的内存大小，这个由优化器统计数据以及前一次执行的性能数据估算得出的
9.User-Mem:语句最后一次执行中，当前操作所使用的内存工作区大小，括号里面为(发生磁盘交换的次数,1次即为One-Pass,大于1次则为Multi_Pass,如果没有使用磁盘，则显示OPTIMAL)
总结）
优点：
1.可以清晰的从STARTS得出表被访问多少。
2.可以清晰的从E-ROWS和A-ROWS中得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确。
3.虽然没有专门的输出运行时的相关统计信息，但是执行计划中的BUFFERS就是真实的逻辑读的多少 缺陷：
1.必须要等到语句真正执行完毕后，才可以出结果。
2.无法控制记录输屏打出，不像autotrace有 traceonly 可以控制不将结果打屏输出。
3.看不出递归调用的次数，看不出物理读的多少（不过逻辑读才是重点）
方式四-通过dbms_xplan.display_cursor输入sql_id参数直接获取
用法）
步骤1： select * from table(dbms_xplan.display_cursor('&sq_id')); （该方法是从共享池里得到） 注：
- 还有一个方法，select * from table(dbms_xplan.display_awr('&sq_id'));（这是awr性能视图里获取到的）
- 如果有多执行计划，可以用类似方法查出
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',0));
select * from table(dbms_xplan.display_cursor('cyzznbykb509s',1));
例子）
select * from table(dbms_xplan.display_cursor('1a914ws3ggfsn'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
SQL_ID 1a914ws3ggfsn, child number 0
-------------------------------------
SELECT * FROM t1, t2 WHERE t1.id = t2.t1_id AND t1.n in(18,19)
Plan hash value: 3532430033
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2 | 8138 | 6 (0)| 00:00:01 |
| 3 | INLIST ITERATOR | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 4056 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1_N | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | T2_T1_ID | 1 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 2041 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(("T1"."N"=18 OR "T1"."N"=19))
6 - access("T1"."ID"="T2"."T1_ID")
总结）
优点：
1.知道sql_id立即可得到执行计划，和explain plan for 一样无需执行；
2.可以得到真实的执行计划。 缺陷：
1.没有输出运行时的相关统计信息（产生多少逻辑读，多少次递归调用，多少次物理读的情况）；
2.无法判断是处理了多少行；
3.无法判断表被访问了多少次。
方式五-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 （格式化命令）
例子）
set autotace off
alter session set statistics_level=typical;
alter session set events '10046 trace name context forever,level 12';
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19);
alter session set events '10046 trace name context off';
select d.value
|| '/'
|| LOWER (RTRIM(i.INSTANCE, CHR(0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
from (select p.spid
from v$mystat m,v$session s, v$process p
where m.statistic#=1 and s.sid=m.sid and p.addr=s.paddr) p,
(select t.INSTANCE
FROM v$thread t,v$parameter v
WHERE v.name='thread'
AND(v.VALUE=0 OR t.thread#=to_number(v.value))) i,
(select value
from v$parameter
where name='user_dump_dest') d;
exit
tkprof d:\oracle\diag\rdbms\test11g\test11g\trace/test11g_ora_2492.trc d:\10046.txt sys=no sort=prsela,exeela,fchela
SELECT *
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t1.n in(18,19)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 12 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 12 0 2
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 94
Rows Row Source Operation
------- ---------------------------------------------------
2 NESTED LOOPS (cr=12 pr=0 pw=0 time=0 us)
2 NESTED LOOPS (cr=10 pr=0 pw=0 time=48 us cost=6 size=8138 card=2)
2 INLIST ITERATOR (cr=5 pr=0 pw=0 time=16 us)
2 TABLE ACCESS BY INDEX ROWID T1 (cr=5 pr=0 pw=0 time=0 us cost=2 size=4056 card=2)
2 INDEX RANGE SCAN T1_N (cr=3 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108621)
2 INDEX RANGE SCAN T2_T1_ID (cr=5 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 108622)
2 TABLE ACCESS BY INDEX ROWID T2 (cr=2 pr=0 pw=0 time=0 us cost=2 size=2041 card=1)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 1.31 1.31
总结）
优点：
1.可以看出SQL语句对应的等待事件
2.如果SQL语句中有函数调用，SQL中有SQL，将会都被列出，无处遁形。
3.可以方便的看出处理的行数，产生的物理逻辑读。
4.可以方便的看出解析时间和执行时间。
5.可以跟踪整个程序包
缺陷:
1.步骤繁琐，比较麻烦
2.无法判断表被访问了多少次。
3.执行计划中的谓词部分不能清晰的展现出来。
方式六-awrsqrpt.sql
用法）
步骤1：@?/rdbms/admin/awrsqrpt.sql
步骤2：选择你要的断点（begin snap 和end snap)
步骤3：输入你的sql_id
总结）
生成完整的执行信息，还包括数据在运行期间的各种信息。实在找不到优化的点，可以打开这个awr报表进行查看。
6种方式适用场景的总结
1.如果某SQL执行非常长时间才会出结果，甚至慢到返回不了结果，这时候看执行计划就只能用方法1；
2.跟踪某条SQL最简单的方法是方法1，其次就是方法2；
3.如果想观察到某条SQL有多条执行计划的情况，只能用方法4和方法6；
4.如果SQL中含有多函数，函数中套有SQL等多层递归调用，想准确分析，只能使用方法5；
5.要想确保看到真实的执行计划，不能用方法1和方法2；
6.要想获取表被访问的次数，只能使用方法3；
如何解读解释计划
单分支解释计划
operation由上一个operation产生，且每个operation有且仅有一个下级operation。如下例：
SELECT deptno, count(*)
FROM emp
WHERE job = 'CLERK' AND sal < 1200
GROUP BY deptno;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5k3rhmx9hprh7, child number 1
-------------------------------------
SELECT deptno, count(*) FROM emp WHERE job = 'CLERK' AND sal < 1200
GROUP BY deptno
Plan hash value: 3067371962
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 2 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 2 |00:00:00.01 | 2 | 888K| 888K| 539K (0)|
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 3 | 3 |00:00:00.01 | 2 | | | |
|* 3 | INDEX RANGE SCAN | EMP_JOB_I | 1 | 4 | 4 |00:00:00.01 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SAL"<1200)
3 - access("JOB"='CLERK')
Note
-----
- cardinality feedback used for this statement
单分支的解读比较简单：读取顺序从ID大到小依次进行解读 ，所以上面的例子解读：首先通过EMP_JOB_I索引（job列）进行范围扫描找到4条数据，由于sal缺乏索引索引，需要进行回表查找，调用TABLE ACCESS BY INDEX ROWID，后只得到一条数据。而又进行group by找到所求。
多分支，分支间无关联
有多个孩子的操作定义为多分支操作，其中孩子的操作互相独立，我们定义为无关联多分支操作
set autotrace off
ALTER SESSION SET statistics_level = all;
SELECT ename FROM emp
UNION ALL
SELECT dname FROM dept
UNION ALL
SELECT '%' FROM dual;
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID 781xq971h0y2p, child number 0
-------------------------------------
SELECT ename FROM emp UNION ALL SELECT dname FROM dept UNION ALL SELECT
'%' FROM dual
Plan hash value: 4181933179
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 19 |00:00:00.01 | 16 |
| 1 | UNION-ALL | | 1 | | 19 |00:00:00.01 | 16 |
| 2 | TABLE ACCESS FULL| EMP | 1 | 14 | 14 |00:00:00.01 | 8 |
| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 4 |00:00:00.01 | 8 |
| 4 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------
从上面的例子我们可以看到，2，3，4是1的孩子操作，但是这三个之间的操作是互相独立，没有依赖的。 所以读取顺序可以是2-3-4-1，也可以4-3-2-1或者3-2-4-1
多分支，分支间相互关联
有多个孩子的操作，且孩子的操作不是互相独立的，我们定义为关联多分支操作，请看下面的例子
SELECT /*+ ordered use_nl(dept) index(dept) */ *
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND emp.comm IS NULL
AND dept.dname != 'SALES';
SELECT * FROM table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
SQL_ID 3ux4g340c933p, child number 0
-------------------------------------
SELECT /*+ ordered use_nl(dept) index(dept) */ * FROM emp, dept WHERE
emp.deptno = dept.deptno AND emp.comm IS NULL AND dept.dname != 'SALES'
--11g执行计划---
Plan hash value: 2694310824
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 |00:00:00.01 | 22 |
| 1 | NESTED LOOPS | | 1 | | 8 |00:00:00.01 | 22 |
| 2 | NESTED LOOPS | | 1 | 10 | 10 |00:00:00.01 | 12 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 10 | 10 |00:00:00.01 | 8 |
|* 4 | INDEX UNIQUE SCAN | DEPT_PK | 10 | 1 | 10 |00:00:00.01 | 4 |
|* 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 10 | 1 | 8 |00:00:00.01 | 10 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."COMM" IS NULL)
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
5 - filter("DEPT"."DNAME"<>'SALES')
在id=1的netsted loops下，2，5是关联的操作，即2操作最终返回多少行，会和5直接进行netsted loops的操作。解读的过程，从0开始扫描是否有子节点，有先读取子节点的操作，从上往下一次准寻这样的顺序。所以上面的例子首先通过表扫描的形式，过滤"EMP"."COMM" IS NULL得到10条数据，接着进行俩表链接采用netsted loops进行俩表的关联，由于3返回了10条数据4会被访问10次。最终2操作得到10条。返回后的结果集作为结果和"DEPT"."DNAME"<>'SALES'过滤的dept结果进行netsted loops，所以5操作也要进行10次的表访问。读取顺序也只能是3-4-2-5-1
总结
整个读取的过程就是递归调用的顺序，先找根节点，根节点如果有子节点，就在找子节点，如果子节点的子节点没有操作，则读取该节点的内容，在访问该节点的兄弟节点，读取内容同上面的操作。 
说明红色会访问路径，蓝色为读取结果后，数据传递路径
解释计划关注的是什么
- 返回行与逻辑读的比例
一般而言，每获取一行开销5个以下的逻辑读是属于基本比较满意的。如果返回的行数只有1条返回确有大量的逻辑是有问题 - 评估值是否准确
错误的评估往往意味着低效的执行计划。如果A-Rows和E-Rows差别太大说明收集的统计信息有一定的问题 - 是否存在类型转换
请关注执行计划中的Predicate Information (identified by operation id)部分，如果出现函数转换一般意味着无法走索引，除非用了函数索引 - 关注recursive calls 如果有较高的（recursive calls）意味需要多次访问数据库的元数据信息，eg硬解析较多
- 表的访问次数
如果starts过多，意味需要频繁的进行物理读，访问表，存在IO上的问题 - 观察是否排序
排序在一般数据库中是一个较大开销的操作，所以需要谨慎关注 7，关注真实返回的行
如果在中间结果返回较多的行，最终确返回较少的行，可能索引需要进行调整。
Original url: Access
Created at: 2018-10-18 11:38:32
Category: default
Tags: none
未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到
最新评论