I couldn’t find any definitive guides on how to compile PL/SQL natively on Windows, so after reading lots of documentation, I finally managed to figure it out. You will need the following:

- Visual Studio .NET 2003. .NET 2005 does not work (at least from what I’ve read), and if you use the x64 version of Oracle, MinGW will not work either. It’s gotta be Visual Studio .NET 2003 with the C++ component installed (ALL components, including the unchecked win platform one)
- Microsoft SDK for Windows. Our boxes are Windows Server 2003 R2, so I downloaded the corresponding SDK for it.

- Important: Make sure when you install the Microsoft SDK, you install it into a directory called ‘C:Program FilesMicrosoft Platform SDK’. The default will be something like ‘C:Program FilesMicrosoft Platform SDK for Windows Sever 2003 R2′; you do not want this! Oracle will look specifically for the directory named ‘C:Program FilesMicrosoft Platform SDK’.

- Important: Make sure you have enough RAM to increase your SGA and PGA memory size. I’ve had cases where I’ve used native compilation only to find that the native version runs slower than the interpreted complation! Or worse, your programs will just crash.

Install both, and open up a sqlplus session (probably under the ‘system’ user), and run the following:

   alter system set plsql_native_library_dir='C:oracledb10gnative' scope=both;
   alter system set plsql_native_library_subdir_count=5 scope=both;
   alter system set plsql_code_type='NATIVE';

- plsql_native_library_dir should be pointing to a directory you specify. I created a ‘native’ directory in my Oracle installation and pointed it there. All the compiled PL/SQL will go into that directory.
- plsql_native_library_subdir_count determines how many directories to use when generating the compiled objects. The reason why this exists is if you have a large amount of procedures / packages to be compiled (large as in > 10,000), there will be I/O issues if you store all the compiled items in one directory. If you specify the count to be more than one, then each compiled item will be put in a subdirectory, round-robin style.
- Since plsql_native_library_subdir_count=5 in the example above, you need to manually create 5 directories in the ‘native’ directory created, called dn (where n is a number starting with 0 to subdir_count) ie:

C:Oracledb10gnatived0
C:Oracledb10gnatived1
…
C:Oracledb10gnatived4

At this point, try compiling your PL/SQL packages or procedures. You can check your subdirectories to see that things are being compiled.

Congrats! You should now have a (slightly) speedier Oracle system. Unfortunately, there are no speed improvements using native compilation for SQL statements. Instead, you need to do your standard optimization techniques for your schemas, such as creating proper indexes, partitions, etc. If you use heavy algorithms PL/SQL, native compilation will benefit this most.

And, if in the event you want to switch out of native compilation, use the following:

ALTER SYSTEM SET plsql_compiler_flags = 'INTERPRETED';
ALTER SYSTEM set plsql_code_type='INTERPRETED';

For more information, see the Oracle PL/SQL NComp document.