案例6　“抽丝剥茧”找出问题所在

1.案例说明

这个案例本身不是为了说明某种技术，而是展现DBA在分析处理问题时的一种方式。其采用的方法往往是根据自己掌握的知识，分析判断某种可能性，然后再验证是否是这个原因。在不断地抛出疑问、不断地验证纠错中，逐步接近问题的本质。

这是某数据仓库系统，有一个作业在某天出现较大延迟。原来作业只需要运行十几分钟，现在需要运行2个多小时，这是业务不能接受的。为了不影响明天的业务系统，必须在今天解决这个问题。经和开发人员的沟通，该业务的SQL语句没有修改，相关的数据结构也没有变更相类似的其他业务（SQL语句相似的）也都正常运行，数据库系统本身也没有异常。

在排除了诸多异常后，这个问题似乎变得很棘手，原本运行正常的SQL语句，忽然在某一天变得异常缓慢。针对这个问题，我采取步步为营的策略，逐步排除可能的原因，并最终找到问题本质，圆满地解决了该问题。

看下面的代码：

---

INSERT INTO xxx 
SELECT  ...
FROM ...
LEFT JOIN t1 a ON t.product_id = a.product_id AND ...
LEFT JOIN t2 b ON t.product_id = b.product_id AND ...
LEFT JOIN t3 c ON t.product_id = c.product_id AND ...
LEFT JOIN t4 d ON t.product_id = d.spxxid AND ...
LEFT JOIN t5 e ON t.product_id = e.spxxid AND ...
LEFT JOIN t6 f ON t.product_id = f.spxxid AND ...
LEFT JOIN t7 g ON t.product_id = g.spxxid AND ...
LEFT JOIN t8 h ON t.product_id = h.product_idAND ...
LEFT JOIN t9 I ON t.product_id = i.prod_id
LEFT JOIN t10 j ON t.product_id = j.prod_id AND ...
LEFT JOIN t11 k ON t.product_id = k.prod_id AND ...
LEFT JOIN t12 l ON t.product_id = l.prod_id AND ...
LEFT JOIN t13 m ON t.product_id = m.prod_id AND ...
LEFT JOIN t14 o ON t.product_id = o.product_id;

---

这是一个多达15个表的关联查询（非常佩服开发人员，逻辑思维太强了）。查询的结果集有400多万条，并插入目标表中。其中目标表较大，有7亿多条记录，物理大小为380GB。在之前的运行过程中，用时十几分钟。

第一步猜测——执行计划异常导致的问题？（固化执行计划）

最开始想到的方法很简单，既然类似的SQL执行效率没问题，而这个SQL由于其他SQL执行计划偏差较大，可以手工采取固化执行计划的方法。这里使用了抽取OUTLINE的方式，具体方法可参见后面的内容。

其调整后的执行计划如下，跟其他类似SQL的执行计划相同。整个执行计划基本可概括为“HASH JOIN”+“FULL TABLE SCAN”。

---

INSERT INTO RPT_PROD_DAY 
SELECT
/*+
      ...
      ...
      FULL(@"SEL$30069D69" "T"@"SEL$4")
      FULL(@"SEL$30069D69" "O"@"SEL$1")
      FULL(@"SEL$30069D69" "J"@"SEL$21")
      FULL(@"SEL$30069D69" "I"@"SEL$19")
      FULL(@"SEL$30069D69" "F"@"SEL$13")
      ...
      LEADING(@"SEL$30069D69" "T"@"SEL$4" ...
      USE_HASH(@"SEL$30069D69" "O"@"SEL$1")
      USE_HASH(@"SEL$30069D69" "J"@"SEL$21")
      USE_HASH(@"SEL$30069D69" "I"@"SEL$19")
      ...
*/
...

---

采用上述方式处理后，整体运行时长减少了10多分钟，但仍然超过了2个小时。显然，对执行计划异常的判断，不是问题的主因。

第二步猜测——缓存捣的鬼？

进一步检查发现，在执行过程中发现了大量的“db file sequential read”等待事件。这个不太寻常。一般情况下，全表扫描会产生“db file scattered read”等待事件。产生后者的原因通常是在buffer中缓存了大部分数据，优化器才可能决定不使用顺序读的方式从文件中读取数据。因此数据库版本是10g，不能直接干预全表扫描是从缓冲区中读取还是文件中读取（11g是可以的），只能采取其他方式。建议更换相关作业执行顺序，避免缓冲区干扰。经测试，速度还是没有明显提升。第二步猜测失败。

第三步猜测——究竟是哪个对象导致的？

进一步分析SQL执行时的情况，发现忽略了一个关键信息，那就是产生“db file sequential read”等待事件的对象。我想当然地认为全表扫描是表，经检查后发现其是一个索引，而且这个索引是目标表的全局索引，相关聚簇因子非常大，接近表的行数。在插入的过程中，需要大量维护索引成本。此表本身还有另外两个索引，都是本地分区索引，维护成本很低。

跟开发人员沟通后，该索引是前一天临时加入的，且没有通过DBA审核。开发人员个人觉得全局索引效率较高，因此就建成了全局的。后续将此索引修改为本地分区索引。经测试，速度从2个多小时缩减到12分钟，问题得到解决。

2.给我们的启示

·优化SQL就是一个抽丝剥茧找到问题本质的过程。在不断猜测、不断试错的过程中，逐步接近事件的本质。你所掌握的知识点越多，可“猜测”的可能性就越多。

·数据结构的变更要经过DBA的审核，这样可以避免很多问题，也可以尽早发现问题、解决问题。