Oracle PL/SQL优化器是优化匿名块还是只优化存储的程序单元？

Question

我在匿名块中有很多代码，我担心PL/SQL优化器不会优化这些代码。我是否应该将代码从匿名块移动到包中，以确保优化器为我完成所有繁重的任务？

Answer 1

你的标题问题的答案是:是的。优化器优化匿名块以及存储的程序单元。请参阅答案末尾的脚本以验证此陈述。

第二个问题的答案是:是的，你应该把你的代码从匿名块移到包中。

然而，第二个是的原因与性能无关。这与管理大量代码的挑战有关。在块(脚本文件)中管理代码比在包中管理代码要难得多。此外，当您将代码存储在指定的程序单元中时，您可以更充分地利用Oracle数据库为您执行的有关代码的所有操作，包括相关性管理、程序失效、自动重新编译以及使用PL/Scope进行代码分析。

现在，要了解优化器确实在匿名块上发挥了它的魔力，请考虑在12.2中执行的以下语句序列。

首先，我将定时设置为on，并将优化级别设置为可能的最大值(级别2是默认值，执行大部分优化；级别3添加子程序内联，这是PL/SQL开发团队推荐的)。

SET TIMING ON

ALTER SESSION SET plsql_optimize_level = 3
/

在第一个块中，我使用游标FOR循环遍历了一大堆行。优化器应该自动将其编译为与BULK COLLECT语句等效的语句(默认情况下一次提取100行)。

SQL> DECLARE
  2     n   INT;
  3  BEGIN
  4     FOR rec IN (SELECT * FROM all_objects)
  5     LOOP
  6        n := n + 1;
  7     END LOOP;
  8  
  9     DBMS_OUTPUT.put_line (n);
 10  END;
 11  /

Elapsed: 00:00:01.943

所以不到2秒。它是优化过的吗？让我们通过比较来检查一下。

在第二个代码块中，我不再使用游标FOR loop。相反，我显式地声明了一个游标，然后一次迭代一行。通过采用这种方法，优化器不能再安全地将此代码转换为批量获取，因此速度要慢得多。

SQL> DECLARE
  2     n   INT;
  3  
  4     CURSOR obj_cur
  5     IS
  6        SELECT * FROM all_objects;
  7  
  8     r obj_cur%ROWTYPE;
  9  BEGIN
 10     OPEN obj_cur;
 11  
 12     LOOP
 13        FETCH obj_cur INTO r;
 14        EXIT WHEN obj_cur%NOTFOUND;
 15        n := n + 1;
 16     END LOOP;
 17     
 18     CLOSE obj_cur;
 19  
 20     DBMS_OUTPUT.put_line (n);
 21  END;
 22  /

Elapsed: 00:00:04.648

比第一个块慢得多。结论:匿名块是优化的。为了说明这一点，让我们将第一个块的性能与存储过程的性能进行比较：

SQL> CREATE OR REPLACE PROCEDURE count_objects
  2  IS
  3     n   INT;
  4  BEGIN
  5     FOR rec IN (SELECT * FROM all_objects)
  6     LOOP
  7        n := n + 1;
  8     END LOOP;
  9  
 10     DBMS_OUTPUT.put_line (n);
 11  END;
 12  /

SQL> BEGIN
  2     count_objects;
  3  END;
  4  /

Elapsed: 00:00:01.875

大致相同。现在你就知道了:匿名块就像存储的程序单元一样被优化了。享受吧！