oracle - Oracle PL/SQL 优化器是否优化匿名 block 或仅优化存储的程序单元？

Question

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

Answer 1

您的标题问题的答案是:是的。优化器优化匿名 block 以及存储的程序单元。请参阅答案末尾的脚本来验证此声明。

第二个问题的答案是:是的，您应该将代码从匿名 block 移到包中。

不过，第二个"is"的原因与性能无关。它与管理大量代码的挑战有关。管理 block (脚本文件)中的代码比管理包中的代码要困难得多。另外，当您将代码存储在指定的程序单元中时，您可以更充分地利用 Oracle 数据库为您提供的有关代码的所有功能，包括依赖性管理、程序失效和自动重新编译、使用 PL/Scope 进行代码分析。

现在要了解优化器确实对匿名 block 发挥了作用，请考虑在 12.2 中执行的以下语句序列。

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

SET TIMING ON

ALTER SESSION SET plsql_optimize_level = 3
/

在第一个 block 中，我使用游标 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 秒。优化了吗？让我们通过比较来验证一下。

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

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

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

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

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