INSERT-SELECT (Oracle PL/SQL) -性能问题

Question

我有太多的SELECT语句，而只有一个INSERT语句(可能有数百个)，系统的性能很差。

我将用一般的语言解释正在发生的事情和我正在寻找的东西：

考虑到Oracle PL/SQL中的以下两个伪代码，它们中的哪一个将提供最佳性能？

选项A：

INSERT INTO MyTable
  WITH Fields AS (
     SELECT Field1, Field2, ..., FieldN FROM TableA JOIN TableW .... WHERE <condition1>
     UNION ALL
     SELECT Field1, Field2, ..., FieldN FROM TableB JOIN TableX .... WHERE <condition2>
     UNION ALL
     SELECT Field1, Field2, ..., FieldN FROM TableC JOIN TableB .... WHERE <condition3>
     ....
     UNION ALL
     ....
     SELECT Field1, Field2, ..., FieldN FROM TableZZZ JOIN TableB .... WHERE <conditionN>

选项B：

BEGIN
  INSERT INTO MyTable SELECT Field1, Field2, ..., FieldN FROM TableA JOIN TableZ .... WHERE <condition1>
  INSERT INTO MyTable SELECT Field1, Field2, ..., FieldN FROM TableB JOIN TableW .... WHERE <condition2>
  INSERT INTO MyTable SELECT Field1, Field2, ..., FieldN FROM TableC JOIN TableH .... WHERE <condition3>
  ...
  INSERT INTO MyTable SELECT Field1, Field2, ..., FieldN FROM TableZZZZ JOIN TableX .... WHERE <conditionN>
END

我没有放入真实的表名，但我想知道:如果我将当前的选项A更改为选项B，它会给我带来更好的性能吗？我的意思是，在这种情况下，用许多INSERT语句替换UNION ALL是个好主意吗？

Answer 1

上下文切换和Performance

几乎PL/SQL开发人员编写的每个程序都包含PL/SQL和SQL语句。PL/SQL语句由PL/SQL语句执行器运行；SQL语句由SQL语句执行器运行。当PL/SQL运行时引擎遇到SQL语句时，它会停止并将该SQL语句传递给SQL引擎。SQL引擎执行SQL语句并将信息返回给PL/SQL引擎(参见图1)。这种控制转移称为上下文切换，每个切换都会产生开销，从而降低程序的整体性能。

因此，使用第三种方法：

create view MyView as select Field1, Field2, ..., FieldN from TableA join TableB .... where <condition1>

declare
  p_array_size pls_integer := 100;
  type         array is table of MyView%rowtype;
  l_data       array;
  cursor c is select * from MyView;    
begin
    open c;
    loop
    fetch c bulk collect into l_data limit p_array_size;

    forall i in 1..l_data.count
    insert into MyTable values l_data(i);

    exit when c%notfound;
    end loop;
    close c;
end;

Answer 2

除非您的查询在数据库服务器上的内存方面非常特殊，否则选项B不会提高大型查询的性能。

要确保上述情况属实，请尝试让您的DBA在执行查询时检查数据库服务器SGA中发生的情况。如果内存中出现阻塞，那么值得尝试实现选项B。

当我说“内存阻塞”时，我的意思是整个SGA内存已满，因此需要在服务器上进行交换。如果在序列中执行插入操作，则可以在插入操作之间重用SGA。