My Oracle SQL Tuning Roadmap PART4

接PART3：My Oracle SQL Tuning Roadmap PART3

四、SQL执行计划获取 

执行计划就是SQL调优的核心，上面的SQL也是通过看到执行计划走HASH JOIN可能有问题出发的。

那么首先要搞定2个问题：

1、如何获取我要的执行计划（准确的计划）；

2、怎么看懂并找出执行计划里的问题。

4.1 如何获取准确的执行计划

获取SQL执行计划的方式：

EXPLAIN PLAN：

估算

忽略绑定变量

非执行

SQL_TRACE：

真实计划，需要用TKPROF工具解析

可以获得绑定变量值

EVENT 10053：

真实计划

研究执行计划产生的原因

AUTOTRACE：

内部使用EXPLAIN PLAN

DBMS_XPLAN：

dbms_xplan.display_cursor

dbms_xplan.display_awr

真实计划

OTHERS:

如awrsqrpt、sqlt、pl/sql、sql developer、toad等

大家一般怎么获取执行计划？我一般用的较多的是dbms_xplan.display_cursor，优点很明显：
1、获取的是真实执行的计划；
2、多种参数。还可以获取绑定变量的值方便验证。

10053是检查优化器行为的，实在搞不懂为什么走那个计划可以看看，用得较少。

10046可以检查一些等待事件的内容，也可以获取绑定变量，一般用得也比较少。

set autotrace traceonly或者explain,他们的执行计划是同一来源，记住，都来自plan_table，是估算的，可能不是真实执行的计划，可能是不准的。

所以，你看得不对劲了，就得质疑它的准确性，autotrace traceonly的好处是可以看到一致性读，物理读，返回行等，这是真实的。因为可以用一致性读，物理读来验证优化效果。

其他的，比如awrsqrpt等都可以获取执行计划，不过我很少用，特别是plsq developer这种工具，F5看计划，我几乎是不用的，他也是plan table里的估算计划。如果很长，那无法分析。

建议大家看真实的计划，说一点，我经常通过alter session set statistics_level=all或者gather_plan_statistics hint，然后执行sql,然后通过

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));来看实际执行的信息，好处很明显，能够看到执行计划每步的E-ROWS（估算的行）,A-ROWS（真实的行）,STARTS,BUFFER GETS，A-TIME（真实的执行时间）等信息，我们通过对比估算的与真实的差距，可以判断哪些表统计信息可能有问题，执行计划是不是走错了，省的我们自己根据谓词去计算这步到底返回多少行。

注意一点，如果一SQL执行很长时间，通过上面的方式来看计划，我们是可以终止的，比如执行2小时执行不完的SQL，一般我没有耐心，最多5分钟，我就终止。终止完，通过display_cursor也是可以看出执行信息的。

比如某个步骤执行100万次，我这条SQL才能执行完，要3小时才可以，我5分钟执行了100次，我终止了SQL我要看的就是一个比例情况，可以通过这个比例来判断，哪个步骤耗的时间最长，哪里大概有问题，然后解决。

优化器很多限制的，比如刚才的TABLE函数固定返回8168，或者算法限制.....很多不准的，如果算法算出来的与真实差别很大，那可能就会导致问题。统计信息有时候也无法收集准确的，比如直方图，就有很多问题，所以12c的直方图多了几种....之前只有等高和等频直方图。

刚才的set statistics_level直接写会输出结果，我们可以让他不输出结果：

用这种东西看执行计划，有时候很方便找出问题,否则我们自己得手动根据每个步骤对应的谓词，自己写SQL去计算真实返回的行，然后再来比较，用这个，ORACLE全帮我们做好了。

4.2  看懂执行计划执行顺序

一般怎么看执行计划呢？

下面举例说明如何看执行计划，先将执行计划COPY到UE里去。

比如ID=10的继续索引，就被ID=11的挡住了，所以第10步就是入口。

找到入口后，反向光标来，利用平行级别的最上最先执行，然后最右最先执行原则，来看父操作与子操作的关系，移动光标即可。

比如这里的第13步，我只需要定位光标在PARTITION这个P前面，然后向上移动，立马就知道，它的驱动表是ID=5的VIEW，因为他们是对齐的。

然后看看之间的JOIN关系是不是有问题，返回的行估算等。

执行计划最右最上最先执行规则，有个例外，大家知道不？？就是通过以上规则，是不正确的。（标量子查询）

SELECT a.employee_id,

a.department_id,

(SELECT COUNT(*) FROM emp_b b

WHERE a.department_id=b.department_id

) cnt

FROM emp_a a;

比如这个ID=2的在前面，但是它事实上是被ID=3的驱动的，也就是被emp_a驱动的，这违背了一般的执行计划顺序规则，平时注意点就行了，标量子查询谓词里会出现绑定变量，比如这里的：B1，因为每次带一个值去驱动子查询。

搞清楚执行计划怎么干，那么看执行计划看啥？

1、看JOIN的方式

2、看表的访问方式，走全表，走索引

3、看有没有一些经常影响性能的操作，比如FILTER

4、看cardinality(rows)与真实的差距

不要太过于关注COST，COST是估算的，大不一定就慢，小不一定就快，当然比如COST很小，rows返回的都是很小的，很慢。那么，我们可能得考虑统计信息是不是过旧问题。

除了统计信息正确，良好的SQL结构，能够让SQL正确进行查询转换，正确的访问结构，如索引等都是让SQL高效执行的前提条件。复杂！=低效，简单！=高效。让优化器理解，并且有合适的访问结构支持，才是王道！

简单的SQL不是快的保证，复杂的也不一定见得慢，能够生成高效的执行计划才是最重要的，优化SQL，最重要的就是让不好的执行计划变得好。

也就是从多个方面入手，最终达到我们的优化目标。