Go native! PL/SQL native compilation
Why use interpreted PL/SQL when native compiled PL/SQL is so much faster? No reason at all - except that interpreted is the default, and most DBAs never change this. They should.
When stored PL/SQL was introduced in release 7.0, the mechanism was to compile the code to machine code executable within the PL/SQL virtual machine, in the same way that Java code is compiled to run in a Java Virtual Machine. Then at run time, the PL/SQL engine maps the virtual machine calls onto whatever calls are native to the processor on which the code is running. This means that the code is compiled to the same form no matter what your platform, and at run time converted to machine code suitable for your processor (which might be SPARC, x86_64, or something else). This mode of operation (which is the default) is known as "interpreted" execution.
In release 9.x, Oracle introduced the possibility of "native" execution. This means that the PL/SQL is pre-compiled into C, then compiled into machine code suitable for your processor, and then dynamically linked into the Oracle executable at run time. This has to result in faster execution: the task of interpreting the code for the processor is done once, in advance, not every time the code is invoked. In release 9.x, it was a bit awkward: you had to tell Oracle where your C compiler was, where the make file was that controls the process, and where to save the dynamic link libraries that get generated.
In 11.x and 12.x the process is much simpler. Oracle provides its own C compiler and linker, and the executable code is stored in the data dictionary. So all you need do is tell Oracle to use native compilation. Here's an example of the performance difference:
orclz> orclz> create or replace procedure p1 as 2 n number; 3 begin 4 for i in 1..100000000 loop 5 n:=n+1; 6 end loop; 7 end; 8 / Procedure created. orclz> set timing on orclz> orclz> alter procedure p1 compile plsql_code_type=interpreted; Procedure altered. Elapsed: 00:00:00.04 orclz> orclz> exec p1 PL/SQL procedure successfully completed. Elapsed: 00:00:01.15 orclz> orclz> alter procedure p1 compile plsql_code_type=native; Procedure altered. Elapsed: 00:00:00.04 orclz> orclz> exec p1 PL/SQL procedure successfully completed. Elapsed: 00:00:00.45 orclz> orclz>Two and a half times as fast. Not bad.
The general advice must be to convert all your code, and Oracle supplied code, to native compilation. The technique:
1. Set the instance parameter PLSQL_CODE_TYPE=NATIVE in your spfile. That will take care of all new code.
2. Set the compilation flag PLSQL_CODE_TYPE to NATIVE for all existing code, by running the supplied script $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql. You do have to startup in upgrade mode to do this.
3. Recompile all existing code with $ORACLE_HOME/rdbms/admin/utlrp.sql
4. Wait for your users to tell you "Wow! The database is really flying today!"
Try it - you have nothing to lose.
(Demo done on release 22.214.171.124 running on a Windows 8.1 laptop)
Oracle Certified Master DBA