Native PL/SQL compilation is achieved by translating the PL/SQL source code into C source code that is then compiled on the same host machine running the Oracle server. The compiling and linking of the generated C source code is done by tools external to Oracle that are set up by the DBA and/or system administrator.
Enabling native PL/SQL compilation in Oracle Database 10g can be accomplished in as few as three steps:
Step 1: Get a Supported C Compiler
If you don’t already have your platform vendor’s usual C compiler, you’ll have to get one from somewhere. Fortunately, this does not always require a huge investment; if you happen to be running Oracle Database 10g Release 2, you can use the freely downloadable GNU C compiler. Table 20-1 shows just a few of the combinations of compiler, version, and platform that Oracle supports. For the complete list, go to Oracle’s Metalink site and search for the “Certified Compilers” document (doc ID 43208.1).
a Obtained by installing MinGW-3.1.0-1.exe from http://www.mingw.org
With the right combination of luck and spare time, you may be able to get an unsupported compiler to work for native compilation; if you have trouble, though, all Oracle will do is tell you to get a certified compiler. I know that some sites have been able to use GCC on Sun Sparc Solaris with Oracle9i Database, but others had trouble until they got Sun’s compiler. And I have never heard of anyone getting GCC working with Oracle Database 10g Release 1.
By the way, you cannot reuse the generated object files on another machine, even if it’s the exact same version of the OS and Oracle; you can’t even copy the object files to a different database on the same machine. The object files contain database-specific information and must be generated on the exact same database and machine that will ultimately run the files. Besides, you might have a DDL event that triggers some automatic recompiles. You will, therefore, need a C compiler on every machine on which you want to use this feature. And, if you happen to be running an Oracle Real Application Cluster (RAC), you’ll need to install your C compiler on each node.
Step 2: Set Up the Directories
When Oracle translates your PL/SQL into C and runs it through the host compiler, the resulting object files have to go somewhere on the server filesystem. Curiously, there is no default for this location; the DBA must create the directories and set one or two initialization parameters. Here is a simple case:
# While logged in as oracle (to get the correct ownership):
$ sqlplus "/ as sysdba"
Some filesystems start to choke on a few thousand files in a single directory; to support that many modules, you can get Oracle to spread the object files across many subdirectories. To use 1,000 subdirectories, specify:
SQL> ALTER SYSTEM SET plsql_native_library_subdir_count = 1000;
You will also need to precreate the subdirectories, which in this case must be named d0, d1, d2...d999. Do this to generate a directory-creating script (using a variation on Oracle’s suggested method):
SET DEFINE OFF
Then, edit out the cruft at the top and bottom of the script, and at the operating system prompt, do something like this:
$ cd /u01/app/oracle/oracle/product/ 10.2.0/db_1/dbs/ncomps
Starting with Oracle Database 10g Release 1, the master copy of the object files is really BLOB data in a table named ncomp_dll$; the on-disk copy exists so it can be dynamically loaded by the operating system. With this capability, Oracle can regenerate the on-disk copies without recompiling the source, but you still don’t want to delete any of the generated files unless your database is shut down.
Step 3: Check $ORACLE_HOME/plsql/spnc_commands
Oracle Database 10g invokes the C compiler by calling a script named spnc_commands (spnc stands for “stored procedure native compilation,” presumably). This file differs by platform, and in some cases includes inline comments indicating how to use different compilers. You’ll want to inspect this file to see if the path to the compiler executable is correct for your installation.
If you’re running Oracle9i Database, there is a file named spnc_makefile.mk that you will need to inspect instead; that version has a more complicated setup for native compilation (see the sidebar “Native Compilation Prior to Oracle Database 10g”).
Native Compilation Prior to Oracle Database 10g
Native compilation does take longer than interpreted mode compilation; our tests have shown an increase of a factor of about two. That’s because native compilation involves several extra steps: generating C code from the initial output of the PL/SQL compilation, writing this to the filesystem, invoking and running the C compiler, and linking the resulting object code into Oracle.
blog comments powered by Disqus