优化 Oracle 存储过程

Question

我最近被委派优化一些现有的Oracle存储过程。每个存储过程都查询数据库并生成一个XML文件输出。其中一个存储过程需要大约20分钟才能完成执行。仔细查看后发现存在几个嵌套循环和不必要的查询。例如，与其在内部循环中进行多次查询，不如在外部查询一次并将结果缓存起来。我重写了存储过程并使用了这种方法。执行时间现在已经降至1分钟以下。

SELECT * from Employee e, Department d WHERE e.DEPT_ID = d.ID
--write data from query to XML

这更像是

FOR emp_rec in ( SELECT * from employee )
LOOP
   SELECT * from Department WHERE id = emp_rec.DEPT_ID;
   --write data from query to XML
END LOOP;

将所有这些情况更改为更像第一种选项的外观，极大地加快了程序的速度。我的问题是为什么？为什么在选择查询中执行连接比手动组合表更快？背后的过程是什么？

- bjsample

Answer 1

让我们来看一下原始版本可能会如何处理。

FOR emp_rec in ( SELECT * from employee )
LOOP
   SELECT * from Department WHERE id = emp_rec.DEPT_ID;
   --write data from query to XML
END LOOP;

循环查询可能会对employee进行全表扫描。然后，对于返回的每一行，它将执行内部查询。假设id是department的主键，则每次执行查询时，都会使用主键索引进行唯一查找。

听起来很好，不是吗？唯一索引查找通常是获取单个行的最快方法（除了显式按ROWID查找）。但是请考虑在循环的多次迭代中所做的工作。假定每个员工都属于一个部门；每个部门都有员工；大多数或所有部门都有多个员工。

因此，在循环的多次迭代中，您正在多次重复内部查询的完全相同的工作。是的，数据块可能会被缓存，所以您不必重复物理读取，但是在缓存中访问数据确实需要一些CPU开销，当相同的块一遍又一遍地访问时，这些开销可能变得非常显着。

此外，最终您可能至少想要一次检索department中的每一行，并且可能超过一次。由于需要读取表中的每个块，因此通过进行索引查找并没有真正节省工作-反而增加了工作量。

当您将循环重写为单个查询时，优化器能够考虑到这一点。它的一个选项是以employee为驱动进行嵌套循环连接，这与PL/SQL中的显式循环基本相同（如Mark所指出的上下文切换除外）。但是，鉴于两个表之间的关系以及缺乏任何过滤谓词，优化器将能够确定直接全表扫描两个表并执行合并或哈希连接更加高效。这实际上会导致更少的物理IO（假设在每次执行开始时都有干净的缓存）和更少的逻辑IO。

- Dave Costa

---

很好的回复。比我提供的答案更好... :-) - Mark J. Bobak

Answer 2

“底层进程”需要一个详细的回答。我会让Tom Kyte来回答这个问题 ;)

- tbone