Skip to Main Content
  • Questions
  • PLSQL calling SQL running slower in 12c (r2) than 11g (r1)

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: March 30, 2017 - 2:51 pm UTC

Last updated: April 13, 2017 - 1:24 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Hi there,

I recently installed Oracle 12c release 2 on a Microsoft Windows 2012 R2 64bit server 16gb RAM and I created a database on this server, into which I imported data from a 11gR1 (11.1.0.6) database that's running on Microsoft Windows 2003 R2 64bit 12gb RAM. Performance of SQL is comparable but when it comes to running plsql code, it's considerably slower on 12c.
Simple test code and output for both environments is below (nb: I've appended the 11g Elapsed times to those on 12c just to save you scrolling up and down). 6 million dollar question - where should I be looking to find out why this is happening?

I should add both servers are VM's running on different physical hosts (but both hosts are identical).


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



Many thanks,
John

and Connor said...

I think something is not right with your 12.2 install. I get figures equal to or better than your v11 numbers, and thats just on my laptop, both on Windows port, and Linux via Virtual box.

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



Perhaps try running the PL/SQL profiler on it and seeing where the difference between the two is ... but I suspect there's a platform or OS level difference in there somewhere.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John, April 03, 2017 - 10:57 am UTC

Thanks for your response Connor. I've given the profiler a try and compared results on 12c with various other 11g versions all of which perform better. I've also resorted to trying it on the default database created using DBCA to eliminate anything I did and the results are the same. It seems switching between plsql and sql (and back again) is where the delay is. Upto 4 times longer on 12c. Here are snippets from the output
12c
P#V PLSHPROF Internal Version 1.0
P#! PL/SQL Timer Started
P#C PLSQL."".""."__plsql_vm"
P#X 8
P#C PLSQL."".""."__anonymous_block"
P#X 63
P#C SQL."".""."__static_sql_exec_line5" #5."d8jhv8fcm27kd"
P#! SELECT :B1 FROM DUAL
P#X 192
P#R
P#X 29
P#C SQL."".""."__static_sql_exec_line5" #5."d8jhv8fcm27kd"
P#! SELECT :B1 FROM DUAL
P#X 98
P#R
P#X 15
P#C SQL."".""."__static_sql_exec_line5" #5."d8jhv8fcm27kd"
P#! SELECT :B1 FROM DUAL
P#X 90
P#R
P#X 14
...


and 11g
P#V PLSHPROF Internal Version 1.0
P#! PL/SQL Timer Started
P#C PLSQL."".""."__plsql_vm"
P#X 2
P#C PLSQL."".""."__anonymous_block"
P#X 32
P#C PLSQL."SYS"."DBMS_OUTPUT"::11."GET_LINES"#660bd56a1b1640db #180
P#X 11
P#R
P#X 28
P#R
P#X 1
P#R
P#C PLSQL."".""."__plsql_vm"
P#X 2
P#C PLSQL."".""."__anonymous_block"
P#X 29
P#C SQL."".""."__static_sql_exec_line5" #5
P#X 74
P#R
P#X 16
P#C SQL."".""."__static_sql_exec_line5" #5
P#X 28
P#R
P#X 4
P#C SQL."".""."__static_sql_exec_line5" #5
P#X 23
P#R
...

and in some of the 12c output there were spikes where values for P#X > 300!
Any suggestions for DB or OS parameters that may be behind this would be much appreciated.
Thanks,
John
Connor McDonald
April 13, 2017 - 1:24 am UTC

Hi John,

I've asked around some of the PM's, and we cant see any evidence of this anywhere.

All I can suggest is logging an SR - they might be able to do some detailed tracing for you.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library