首页 > Oracle > oracle 优化之subquery unnesting.
2013
10-14

oracle 优化之subquery unnesting.

  subquery unnesting,也就是子查询非嵌套,在阐述这个概念之前,我们先谈一下filter,我这里所说的filter并不是执行计划中的谓词过滤里面那个filter,而是operation里面的filter,下面我们直接从一个例子来看一下filter:

1. 创建测试表

2. 打开trace,跟踪一下sql的执行时间,在sql执行的时候,大家可以另外开一个session,看一下服务器CPU的负载情况,我这里的测试虚拟机只有一个cpu,负载是95%以上

我这里的测试机cpu虽然不及服务器,但sql的实行过程中,执行速度还是很慢的,漫长的等待后,我们来看一下sql的执行时间和执行计划

可以从执行计划中看出,执行花了9分7秒的时间,大家注意看执行计划中id=1,operation=filter,2个表均采用的全表扫描,当然了,我这里还没创建索引,我们看一下2个表的数据量,可见数据量都是比较小的

3. 但是数据量这么小,为什么很简单的sql确执行了很久,9分多种算比较久了吧。下面先看一下创建索引的情况

从上面2个执行计划分析,如果不加索引,test1全表扫描91563行数据,由于两表操作的filter(相当于嵌套循环),那test2表就要被扫描91563次,通过如下sql可以看到test2表有13MB,那这条查询语句总计要处理91563*13/1024=1162.42GB的数据,在buffer cache中要处理这么多数据,如果放在生产库上,也吃不消吧。估计一天都不会有结果。

在创建索引之后,index range scan一次只扫描几个块,注意看执行计划中谓词信息部分3 – access(“OWNER”=’SCOTT’ AND “OBJECT_ID”=:B1),这里有绑定变量,每次驱动表传入一个值,test2就执行索引扫描,但只会扫描root+branch+leaf块,join列的唯一性很高的话,leaf一次就只扫描一个块,这样算下来索引扫描:91563*3个块。

现在大家如果在执行计划中看到有filter的情况,要注意分析,然后针对优化。

这种filter应该比较好理解吧。这种简单的也比较好优化。上面简单的理解了filter,我们再来深入一下subquery unnesting。

子查询非嵌套,当sql语句中的where条件后面带有子查询,而且子查询里面有in,not in, exists, not exists,<,<=, >,>=等,CBO就可能会对该子查询进行等价改写,这个过程就叫子查询扩展,之所以oracle要改写,是因为CBO认为sql语句进行改写后,能更好的优化,效率更高,当然也并不是所有的子查询都会被改写,子查询中的某些限制条件会限制CBO进行改写,因为改写之后可能不等价。

1. 下面我们直接看一个例子,scott用户下的emp和dept表

2. 我们再来看另一个sql的执行计划,2个sql写法不一样,但是等价的

可以看出来这2个sql的执行计划是一模一样的,当我们执行第一个sql语句的时候,CBO会对sql进行等价改写,这个过程就叫subquery unnesting。当然改写之后的sql执行效率不一定是最好的,如果在实际的工作中,遇到这种被改写了的情况,可以用hint (no_unnest)禁止CBO进行subquery unnesting,也可以用unnest提示CBO进行subquery unnesting,另外也可以修改隐藏参数来禁止:alter session set “_unnest_subquery”=false;

如上sql使用了no_unnest提示CBO禁止subquery unnesting,可以从执行计划中看出来2个sql的执行计划是不一致的。

总结:当主表返回数据量大的时候,需要展开,展开之后可以让子查询作为驱动表,如果在执行计划中看见了filter关键字说明没展开。这里所说的filter是operation中的filter。另外要注意这个hint只能放在子查询里面。

最后编辑:
作者:Jerry
一个积极向上的小青年,热衷于分享--Focus on DB,BI,ETL