Nested Loop Join的另外一个性能陷阱

Nested Loop Join是数据库表连接中使用最为频繁的连接方式,其原理就像是我们手头拿着一个是个人的姓名,通过一个已经根据姓名排序的电话号码本去查找这是个人的电话号码。通过索引,我们可以很高效的进行关联检索。不过如果我们手头的一千多页的电话号码本并没有按照姓名排序,而是按照电话号码排序的,那么这项工作就是几乎不可能完成的任务了。这时候如果是SQL语句的话,SQL性能就会出问题了。

Nested Loop Join十分简单,只要确保内表索引的高效就可以确保执行效率了。不过Nested Loop Join也有缺点,比如我们手头拿的是一个20万人的姓名,去找这20万人的电话号码,那没有个把月是搞不定的,这时候就需要用HASH JOIN了。把NLJ和HASH JOIN弄错是NLJ最常见的性能问题。

除此之外,NLJ是不是就是安枕无忧了呢,实际上还不一定,还有一个很容易出问题的地方需要关注。前阵子我谈到过MYSQL数据库有一种NLJ的改良型,就是BNLJ,批量NESTED LOOP JOIN,这种连接方式是在HASH JOIN没出现之前,解决NLJ的性能问题的。实际上Oracle也由类似的NLJ方式,Oracle称为Nested Loop Join Batching。实际上也是为了利用硬件内存,CPU资源来进一步提高存在多个循环的NLJ的性能。这种连接方式从11.2.0.4开始引入,可能很多DBA都没有关注到。不过如果我们仔细观察一下,就可以看到二者之间的区别。

图片

上图是一个典型的NLJ,首先查找外表数据,然后通过索引查找内表数据。而一个NLJ-BATCHING的典型执行计划是这样的。

图片

大家可以看到里面有两层NESTED LOOP循环,外循环是通过对两张表的索引的连接线创建一个批量的集合,然后再和表B进行NESTED LOOP关联。这种连接方式的好处是通过第一层的关联,可以不需要读表,通过索引就完成了以此数据过滤,从而让执行更为高效。

不过任何技术都有双刃剑。

前几天,我们的一个客户的一个业务突然变慢,原本几十毫秒的一个查询,现在几分钟出不来,幸亏出问题时候是半夜,所以只是略微积压了一些业务。后来他们通过把表的统计数据做了更新,解决了这个问题。

图片

从执行计划上看,我刚开始也没发现其中的问题,就是一个典型的NLJ,好像都走了索引。

图片

上面是正确的执行计划,似乎除了在NLJ的内表上选择了另外一个索引,执行计划并没有任何其他的差异,不过执行时间恢复为几十毫秒了。为什么同样的NLJ,性能会有这么大的差异呢?后来在朋友的指点下,我仔细看了一遍执行计划,发现这个NLJ并不是传统的NLJ,而是一个NESTE LOOP JOIN BATCHING的执行计划,也就是Oracle 11.2.0.4之后新引入的高性能Nested Loop Join。

这个执行计划为什么会出现性能问题呢?实际上在MOS上也已经有很多Nested Loop Join-Batching引起性能问题的资料。我们可以通过一个实验来验证一下这个问题。

drop table test_a;

create table test_a (t_date varchar2(8),t_name varchar2(100),t_id number);

create index idx_test_a_1 on test_a(t_date);

create index idx_test_a_2 on test_a(t_id,T_NAME);

insert into test_a  select to_char(sysdate-1,'yyyymmdd'),object_name,object_id from dba_objects ;

commit;



drop table test_b;

create table test_b (t_id number,own varchar2(100),t_stat varchar(20));

create index idx_test_b on test_b(t_id);

create index idx_test_b_1 on test_b(own);

insert into test_b select object_id,owner,status from dba_objects ;

insert into test_b select object_id,owner,status from dba_objects ;

insert into test_b select object_id,owner,status from dba_objects ;

DELETE TEST_B WHERE OWN IN (SELECT OWNER FROM DBA_OBJECTS GROUP BY OWNER HAVING COUNT(*)>20);

commit;



exec dbms_stats.gather_table_stats(ownname=>'xuji',tabname=>'test_a',cascade=>true,estimate_percent=>100);

exec dbms_stats.gather_table_stats(ownname=>'xuji',tabname=>'test_b',cascade=>true,estimate_percent=>100);

首先我们准备好一些数据,然后来验证一下执行计划:

图片

没错,这个SQL采用了NLJ-BATCHING执行计划,很完美,也很高效。下面我们来改变一下数据(同时统计信息没有变化)。

图片

要注意的是,我们增加了一些t_date=sysdate+1的数据,这些数据在统计数据里是没有体现的,从而我们试图引发性能问题。

图片

因为统计数据没有更新,所以,执行计划还是那个计划,不过执行时间已经从0:0.0变为00:02.13了,逻辑读也达到了412万,明显这个执行计划是不合理的。我们通过提示,关闭新的NLJ的模式,同时指定必须使用NLJ,来看看效果。

图片

性能又恢复了,逻辑读只有67了。为什么会有这样的差距呢?因为新的NLJ-BATCHING可能会引入笛卡尔集,当外循环的两个索引扫描后的结果集都很小时,笛卡尔积是十分高效的,消耗一点内存和CPU就可以了。而当统计信息存在错误,错误的选择了这个执行计划的时候,大量数据进行笛卡尔积计算,那就是灾难了。

从上面的这个例子,我们可以学到一些东西了。随着CBO优化器越来越强大,各种提升性能新的执行方式确实能帮助我们,但是如果我们的系统存在某些缺陷,这些新功能也可能成为性能杀手。最后我们总结几条今天这个话题的要点:

l统计信息的准确性十分重要,否则NLJ-BATCHING很可能成为一个新的SQL性能杀手

l一些应用系统中的表中索引设计存在问题,有很多性能相近的索引,一旦统计信息不准确,导致NLJ-BATCHING选择错了索引的时候,引发灾难性的后果的可能性很大。因此在性能敏感的核心业务表的索引设计也要十分小心,从而避免出现类似问题

l一些核心交易系统的每天数据变化都很大,而统计分析往往是一天做一次的,因此在一些比较复杂的环境中,很可能因为NLJ-BATCHING中存在的笛卡尔积问题而导致严重的问题,在这些环境众,可以尝试通过_nlj_batching_enabled参数,关闭这种新的NLJ执行计划,从而避免此类问题发生

发表评论

登录后才能评论
网站客服
网站客服
申请收录 侵权处理
分享本页
返回顶部