一次merge的优化经验

现在我们生产环境有这么个性能问题：随着业务量增大，出口零件配置数量猛增，从以前的几万条猛增到现在的几十万，甚至上百万，以前逻辑在处理零件信息时，是采用循环的形式，每次循环只能处理一笔零件信息，循环里面的逻辑比较复杂，大概的逻辑是新进来的配置信息要与之前的配置进行一次零件信息交换，交换的维度从以前同类型的零件信息随机取一笔，将信息复制到新进来的配置上。没办法，领导天天催着，开始优化吧。

首先可以肯定的是程序的性能瓶颈在于循环次数过多，那么我想到的是用merge，用同类型的一批新零件与旧零件用merge进行信息交换，这样就可以减少循环次数。

想法很好，但实现起来却不是那么顺利，费了九牛二虎之力，将循环中的复杂逻辑终于整到一个merge语句中，结果这个merge语句有一百多行，涉及到十几个表，大概的sql如下：

MERGE INTO line_tmp tl
USING (SELECT line_id,code_id
         FROM (SELECT xxxxx FROM 表A,表B,表C...表N
               WHERE 条件) t1,
              (SELECT xxxxx FROM 表E,表F,表G...表P
               WHERE 条件) t2,
        WHERE t1.num1=t2.num2) tt
ON (tl.source_line_id = tt.line_id)
WHEN MATCHED THEN
  UPDATE SET tl.new_info=tt.old_info;

我满心欢喜，拿到测试环境去进行压力测试，发现测试十几万数据量时，新的性能问题产生了：这个merge语句死活都跑不出来，查看session的等待事件一直为：latch free 查看了相关资料，latch free 等待事件是由于内存中闩资源争用造成的，主要是buffer cache和share pool内存争用。看了半天资料，大多都是从DBA角度去通过设置参数来解决，很显然在我这里不适用。

又经过一段时间抓耳挠腮，突然有个想法，是不是因为我merge语句中用到的表太多造成了latch free？反正死马当活马医，试试看，于是我用了两个临时表，将上面子查询t1和t2的数据分别先存到临时表（新建的实表）temp1，temp2中，然后再merge，大概的sql如下：

MERGE INTO line_tmp tl
USING (SELECT line_id,code_id
         FROM temp1 t1,
              temp2 t2,
        WHERE t1.num1=t2.num2) tt
ON (tl.source_line_id = tt.line_id)
WHEN MATCHED THEN
  UPDATE SET tl.new_info=tt.old_info;

抱着一线希望，再次进行压力测试，数据量还是十几万，结果10分钟就搞定！要知道以前起码得跑2个多小时，我带着窃喜的心情拿另一个产品再做一次压力测试，数据量增加到二十万，结果又让我郁闷了，一个多小时没跑出来！！我这就纳闷了，数据量没增加多少，怎么效果差这么多。我不肯相信，把之前10分钟搞定的产品再拿出来测，结果让我抓狂，也是一个多小时跑不出来！！！！

心情经过过山车般的跌宕起伏，我做出了一个重要的决定：出去抽支烟。。。。。抽完烟果然冷静下来，我把之前成功merge的执行计划和现在的做了下对比，发现两者的执行计划存在明显的差异：成功的执行计划：先将temp1，temp2做hash连接，再与line_temp做hash连接，这很显然是正确的执行计划，因为temp1和temp2的数据量在2W左右，line_temp的数据量为十几万（line_tmp其实就是新零件信息存放的临时表），做hash连接是最明智的。。再看看现在错误的执行计划：temp1与line_tmp先做嵌套连接，再与temp2做嵌套连接，用脚趾想想用这个执行计划肯定跑不出来。

但是为什么oracle会选择这种错误的执行计划了？我想到了统计数据，我把当时temp1的统计数据抽出来看了下，发现统计数据里面记录temp1的rownum为1！！！难怪它会选择先与line_temp做嵌套连接。这时我突然明白了另一个问题，就是为什么oracle的统计数据会不准？因为merge的3个表都是临时表，临时表的数据是程序开始时插入，程序结束后清除，所以oracle很难收集到正确的统计信息，临时表数据量变化太大了。这也就解释了，为什么第一次能跑出来，后面几次跑不出来了。

问题弄清楚了，现在的关键就是如果引导oracle将这个merge语句用hash连接去执行？首先我想到用hint   /*use_hash*/，发现oracle根本不领情，还是用嵌套连接。这时我又想到hash连接的一个典型例子，如下：这种类型sql一般都是走hash连接

SELECT *
  FROM a,b
 WHERE a.id=b.id

于是我又把temp1和temp2整合成一个临时表temp3，sql大概如下：

MERGE INTO line_tmp tl
USING temp3 tt
WHEN MATCHED THEN
  UPDATE SET tl.new_info=tt.old_info;

经过测试，发现这样写确实可以引导oracle走hash连接，而且执行计划很稳定！最后测试了一个一百多万配置的产品，半个小时就跑出来了！！！效果真棒！！！

功夫不负有心。。。。。折腾这么久，终于还是弄出来了，心理还是很高兴