前言：又一个时快时慢的SQL，不过这一次，10g与11g执行计划不一致，11g执行计划一致却还是时快时慢，如果遇到这样的问题，你会从哪分析入手，如何给出合理的解释，如何给出进一步优化的建议呢？本期，我们继续有请我们的优化老能手老猫与我们分享又一例经典SQL案例。
客户Q ： 『老猫，我这里有个非常诡异的问题，从10g升级到11.2以后，有条SQL，晚上运行非常慢；白天就很快。数据量变化不大，一天最多增加10几条数据。』
老猫 ： 『那么，在10g上，是快还是慢？』
这么神奇的问题？对此，没有看到数据，老猫不发表评论。
远程到客户环境，查看SQL在不同情况下的执行情况，总结SQL执行特征如下：
11g
晚上的情况
平均每次的buffer gets: 29349
平均每次返回行数: 16行
11g白天的情况
平均每次的buffer gets: 3
平均每次返回行数: 0行
10G上的情况
平均每次的buffer gets:
4098156
平均每次返回行数: 0行
无
论是白天还是晚上，每次执行的buffer gets都是4098156，返回的行数与11.2的状况差不多。晚上大约10几行，白天为0行。但是，性能一直很差。
没骗你吧!？11g上执行计划也没变，数据变化也没那么大，但是buffer gets相差很大。11g是不是怕光呀？
也许是得了狂犬病，怕光。你养宠物吗？
J
观察问题的特点
“古老”的10G
老猫是个尊重事实的人，我要证明Oracle是不是真的患上了狂犬病。先作个血常规！我们看到10G与11G的执行计划不一样，所以，先看执行计划吧。
10g
11g
老猫调SQL的习惯是不会先看SQL，执行计划往往能更直接地告诉我们问题出在了哪里。
看来10g执行计划和11g执行计划大有不同，逻辑读区别大也是情有可原的了；然而，11G白天晚上的执行计划相似，如果不是大规模的数据量的变化，又是怎么会出现逻辑读相差太多呢？
看完执行计划，其实语句是什么老猫心里也有数了，语句并不复杂：
可以看到，语句涉及的两张表，XXOL和XXXMASTER表，使用
not in
的方式进行关联，关联字段为XXtno；
对于这样一个语句，执行计划的区别就是10g上使用的是filter，而11g上使用的是hash join；
filter
相当于从驱动表（XXOL）中取出一条，然后逐条到被驱动表（XXXMASTER）中进行检测，整个语句执行过程中被驱动表将被访问N次；
hash join
则只需要将驱动表和被驱动表做一次扫描，然后基于hash算法进行等值关联计算即可；
两种连接方式一对比，高下立判；那么这里的问题就变成了，
为什么10g是用filter的方式，而11g却可以实现hash join的方式呢？
not in和not exists
这里filter总是我们不想看到的一种情形，那么怎样给他规避这种情形呢？稍有些经验的同学看到not in 这样的语句，不免会想到oracle中另一种写法not exists；那我们这里不妨用not exists的方式来试试呢？
很显然，如果改用not exists的方式进行关联，在10g的数据库中，我们也能看到很顺利的使用了hash join anti；
然而，如果我们稍加理解，就会知道，
not in和not exists不是可以随意转换的；
我们来简单分析下面的语句：
select * from t1 where id not in (select id from t2 );--SQL1
select * from t1 where not exists (select 1 from t2 where id=t1.id);--
SQL
2
考虑下面的各种情形（
划重点
）：
对于SQL1:
1.在t1表中有id=1的记录，如果在t2表中存在id=1的记录，那么 1 not in {1,...}
结果为false
，id=1的记录将不会返回；
2.在t1表中有id=2的记录，如果在t2表中
只有id={1,3,4,5}
的记录，那么2 not in {1,3,4,5}
结果为true
,id=2的记录将会返回；
3.特殊情况，在t1表中有id=1的记录，如果在t2表中存在id 为null值的情况，对于 1 not in (null,2,3,4...}，
结果为false
，因为 null与任何值关联，结果都是false；
也就是说，对于任意t1表中的记录，如果t2表中存在id为null值的情况，在这种查询的情况下，它都没有返回，整个查询的返回的结果集将是0条
；
4.特殊情况，在t1表中有id 本身为null的记录，null not in {1,2...}，
结果为false
，id为null的记录将不会返回；
对于SQL2：
1.
在t1表中有id=1的记录，如果在t2表中存在id=1的记录，那么 not exists {1=1}
结果为false
，id=1的记录将不会返回；
2.在t1表中有id=2的记录，如果在t2表中
只有id={1,3,4,5}
的记录，那么not exists {1,3,4,5}=2
结果为true
,id=2的记录将会返回；
3.特殊情况，
在t1表中有id=1的记录，如果在t2表中存在id 为null值的情况，对于not exists {null,2,3,4}=1，其中null与任何值关联均为false，再加上一个not exists取反，
结果为true
；id=1的记录将会返回；
4.
特殊情况，在t1表中有id 本身为null的记录，not exists {1,2,3,..null,..}=null，同上，null值关联为false ，加上not exists取反，
结果为true
；id为null的记录将会返回；
简单综合上面的结论，我们可以得知，not in 和not exists的区别其实可以简单粗略来说，not exists的查询最终将会返回关联字段含有
null值
的记录，而not in的查询则正好相反！
所以，如果对于一个SQL，用not in还是not exists，可不能为了图方便或者图性能，就随便改写的，需要考虑关联字段上的null值情况；
11g做了什么
上面我们看到了，在10g中，not in 似乎只能走filter，而not exists则可以轻松走上hash join，但是语义上是有区别的；而在11g中，优化器似乎自动帮改写为hash join了；
难道是，语句本身就可以改写，而在10g中优化器没有发现或者被忽略了？
通过前面的理解，能不能改写，我们看看表上的字段是否已经被强制定义为非null值即可；
这样看来，直接改写not in为not exists是会有问题的？难道11G做了这么一个愚蠢的决定吗？我们不妨仔细来看看11G上的执行计划：
注意到，与我们10g中改写为not exists后执行计划中的"hash join right anti"不同，这里使用的连接方式叫做"
hash join right anti na
"；什么是
NA
呢？实际上就是11g新功能
Null aware
的体现！
神奇的Null aware
什么是null aware呢？字面意思就是发现null值;考虑前面划重点部分针对not in语句的第3点和第4点，oracle在帮助改写"not exists"之后做了额外的操作，在提高效率的同时，保证了结果的正确性；
理解Null aware
实际上理解了前面的null值的结果差异，我们就可以大致理解null aware的实现原理了，对于下面这条SQL：
select * from t1 where id not in (select id from t2 );
如果t1/t2表上的id字段都没有定义not null属性，11g的优化器的null aware功能是怎么做到既高效又准确的呢？老猫给它写的伪代码是这样的：
if
(select count(*) from t2 where
id is null
)>0
return null;
else
return
(select *
from t1
where
id is not null
and not exists (select 1 from t2 where id = t11.id));
end if;
解释：
1.对于t2表（not in 子表），如果有存在id字段（关联字段）为空的情况，不用继续，整个结果集将为空，不需要再访问外层主表；
2.如果t2表没有id字段为空的情况，继续做t1表的查询，不过在查询的过程中也将t1表的null 值去掉，然后做not exists的转换，继续老老实实的做hash join anti；
理解了上面的逻辑，我们也就能推断出客户白天黑夜数据的变化；
是不是白天的时候XXXMASTER表中xxtno列有null值的情况？到了晚上这些null值列就消失了呢？
...若干了解时间过后...
还真是这样的，原来这就是狂犬病毒啊，太神奇了，你是怎么知道的呢？
......%$@*#^%
巴拉
*#^%
......
看来还是11G好!ORACLE太智能了！那这样，晚上慢一些好像也是正常的了？
功能是不错，不过也得防着点bug，记得把补丁打全哟。晚上那不能说慢，只是正常的过程。
开心一笑
福
特汽车公司接到投诉电话，说我买的汽车对香草冰激凌过敏，因为我每次到超市买香草冰激凌，回来时车就打不燃火了，但是我买芒果和巧克力冰激凌就没问题。
客服中心说这是捣乱的，没有理会，直到接到第五次投诉的时候，福特才开始重视，有一个技术工程师自告奋勇去了，说我陪你开车去买冰激凌。结果发现，事实果然如之前所说的那样。
为什么？拿到修理厂也没有发现原因，验证了好几次之后，终于解答了神秘事件：
这辆车的确有故障，系统一旦熄火散热不好，需要5分钟之后才能打燃，芒果味或者巧克力味冰淇淋的销售很好，排队要超过5分钟，所以没有问题。但是香草冰激凌的销售不好，排队的人很少，3分钟就可以买到，这个时间不足以让系统散热，所以打不燃
。
老猫有话说
该升级的时候，就升级。Oracle在每个版本上，不仅仅是修复一些Bug，还作出了很多贴心的好功能。
这个NOT IN（…）的语义，开发人员真的理解了吗？会不会是应用的Bug呢？这需要与开发人员确认。
福特汽车的故事告诉我们，不要轻视客户的描述，往往背后藏着天大的秘密。
本文转载于中亦安图
