如何为oracle循环生成执行计划

Question

如何为 Oracle 循环查询生成执行计划，如下所示：

BEGIN 
  FOR L IN (SELECT FIRST_NAME, LAST_NAME, SOME_ID FROM TABLE1)
  LOOP
      INSERT INTO TABLE2 (FIRSTNAME, LASTNAME)
      (SELECT FNAME, LASTNAME FROM TABLE2 WHERE SOME_ID = L.SOME_ID)

  V_CNT := V_CNT + 1;

  IF (MOD(V_CNT, 1000)=0) THEN
    COMMIT;
  END IF;

END LOOP;

如何为上述块生成执行计划？

我之前问过类似的问题并决定提取块从我的存储过程中并单独生成执行计划。虽然我知道如何为简单查询生成计划，但我不知道如何为上面的循环块生成它们。

Answer 1

您的 PL/SQL 块可以用 SQL 重写：

INSERT INTO table2 (firstname, lastname)
   SELECT fname, lastname
     FROM table2
    WHERE some_id IN (SELECT some_id FROM table1);

然后您可以“解释计划”上面的 INSERT。

如果必须进行批量提交，那么使用 BULK COLLECT ... LIMIT 和 FORALL 比游标 for 循环更有效，尤其是在处理数百万行时。有用的链接：

• BULK COLLECT 子句
• < a href="http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/tuning.htm#LNPLS879" rel="nofollow">FORALL 声明t
• 使用 Oracle 批量插入
• dml 单个插入/选择或批量收集/forall

Answer 2

您无法为 PL/SQL 块生成执行计划，只能为查询生成执行计划。

执行计划显示了声明性命令 (SQL) 是如何在幕后执行的，即使用了哪些过程。它将声明性结构（集合操作）转换为过程（嵌套循环、散列连接、排序等）。

PL/SQL，顾名思义，是一种过程语言，定义执行计划本身 - 在您的情况下是嵌套循环。

如果您想查看内部查询的执行计划，您应该复制它并发出 EXPLAIN PLAN FOR ...

请注意，这可以更有效地重写为：

BEGIN 
        FOR L IN
        (
        SELECT  MIN(some_id) AS minid, MAX(some_id) AS maxid
        FROM    (
                SELECT  some_id, rownum AS rn
                FROM    (
                        SELECT  some_id
                        FROM    table1
                        ORDER BY
                                some_id
                        )
                )
        GROUP BY
                TRUNC(rn / 1000)
        )
        LOOP
                INSERT
                INTO    TABLE2 (FIRSTNAME, LASTNAME)
                SELECT  FNAME, LASTNAME
                FROM    table1
                JOIN    table2
                ON      table2.some_id = table1.some_id
                WHERE   table1.some_id BETWEEN l.minid AND l.maxid
                V_CNT := V_CNT + 1;
                IF (MOD(V_CNT, 1000)=0) THEN
                        COMMIT;
                END IF;
        END LOOP;
END;

Answer 3

如果您对过程 PL/SQL 代码的性能感兴趣，您可能需要研究 PL/SQL Profiling。

Profiler 允许您收集有关 PL/SQL 运行时性能的信息。分析运行完成后，您可以报告结果并查看信息，例如每行执行的次数、每行的平均执行时间。这可以帮助您识别瓶颈，并应该指出您可能需要调整注意的第一件事。

您最大的潜在回报是查看性能最差的代码并使其运行得更好。一旦改进，再次分析以验证您的改进并找到下一个可能需要注意的语句。

一个很好的第一篇介绍在这里： Oracle 的 DBMS Profiler