Oracle PLSQL调用在12c (r2) 中运行的SQL比11g (r1) 慢

你好，

我最近在Microsoft Windows 2012 R2 64位服务器16gb RAM上安装了Oracle 12c版本2，并在此服务器上创建了一个数据库，从运行在Microsoft Windows 2003 R2 64位12gb RAM上的11gR1 (11.1.0.6) 数据库中导入了数据。SQL的性能是相当的，但是当涉及到运行plsql代码时，它在12c上的速度要慢得多。
以下是两种环境的简单测试代码和输出 (注意: 我已将11g经过的时间附加到12c上，以节省您上下滚动)。600万美元问题-我应该在哪里寻找为什么会发生这种情况？

我应该添加两个服务器都是在不同物理主机上运行的VM (但两个主机都是相同的)。

12.2.0.1.0
SQL> select 1 from dual;

         1
----------
         1

Elapsed: 00:00:00.01 (11.1.0.6: 00:00:00.00)

12.2.0.1.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    select 'x' into x from dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00 (11.1.0.6: 00:00:00.00)

12.2.0.1.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..1000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14 (11.1.0.6: 00:00:00.03)

12.2.0.1.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..10000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.12 (11.1.0.6: 00:00:00.20)

12.2.0.1.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..100000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.54 (11.1.0.6: 00:00:01.51)

12.2.0.1.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..1000000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:29.06 (11.1.0.6: 00:00:15.12)

12.2.0.1.0
SQL> spool off


11.1.0.6.0
SQL> select 1 from dual;

         1
----------
         1

Elapsed: 00:00:00.00

11.1.0.6.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    select 'x' into x from dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

11.1.0.6.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..1000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

11.1.0.6.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..10000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.20

11.1.0.6.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..100000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51

11.1.0.6.0
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..1000000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.12

11.1.0.6.0
SQL> spool off

复制

非常感谢，
约翰

我认为您的12.2安装不正确。我得到的数字等于或优于您的v11数字，这仅在我的笔记本电脑上，无论是在Windows端口上，还是通过虚拟盒在Linux上。

SQL> set timing on
SQL> declare
  2    x varchar2(30);
  3  begin
  4    select 'x' into x from dual;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL>
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..1000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..10000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10
SQL>
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..100000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.93
SQL>
SQL> declare
  2    x varchar2(30);
  3  begin
  4    for i in 1..1000000 loop
  5      select i into x from dual;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.07
SQL>
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production

5 rows selected.

Elapsed: 00:00:00.02
复制

也许尝试在其上运行PL/SQL profiler，看看两者之间的区别在哪里...但我怀疑那里有平台或操作系统级别的差异。