Native compilation for Oraclie 9i
Just notes to self on setting up an Oracle 9i/Solaris server to do native compilation instead of interpreted PL/SQL, since I spent a couple of hours on it last night, and I can see this coming up again in the future. The main source is this Oracle document. A set of $ORACLE_HOME/rdbms/admin scripts can be found off of this doc, which will convert existing Oracle-internal stored procs and such to native compilation.
The first part is to add the following parameters to the init.ora/spfile:
plsql_compiler_flags=NATIVE plsql_native_library_dir=some_directory plsql_native_library_subdir_count=1000 plsql_native_make_file_name=some_directory/spnc_makefile.mk plsql_native_make_utility=/usr/local/bin/make
The spnc_makefile.mk can be found in $ORACLE_HOME/plsql, and can be copied to the appropriate directory and modified for local usage. By default, the Makefile uses Forte as the C compiler. I pulled down the 64-bit GCC 3.3 from sunfreeware, or, rather, FTPed it from the sunsite.unc.edu mirror.
The modifications to the Makefile are relatively simple, though not obvious for the first time through: CC has to be set to gcc, of course, and the OPTIMIZE and PIC options for gcc have to be uncommented, and the Forte ones commented out. 64-bit gcc also requires the -m64 flag, which can be put in the OPTIMIZE variable; this is apparently necessary for 64-bit Oracle or there’s an ELF class error when running the make.
Note that a whole bunch of subdirectories have to be made in the plsql_native_library_dir. With the above parameters, 1000 are needed, with names in the form of d0, d1, d2, … d999. Clearly, something like a perl script is the best way to do this. Oracle puts the resulting .so files into these directories, apparently randomly. 1000 subdirs is apparently what you want for production boxes. I’m not sure what the rationale is; presumably something along the lines of directory listings being read quickly, or something similar.
At this point, go into sqlplus as sysdba, and do “@?/rdbms/admin/dbmsupgnv.sql” to start off the compilation of the PL/SQL packages. If there’s a real problem, the compilation bombs out after a few minutes. If it works, it runs for quite some time, though there are a number of warnings on mv commands, as Oracle tries to backup non-existent files before recompilation. On a Sun E480, running the make took about an hour.