by Roger Schrag
Database Specialists, Inc.
About Database Specialists, Inc.
Database Specialists, Inc. provides remote DBA services and onsite database support for your mission critical Oracle systems. Since 1995, we have been providing Oracle database consulting in Solaris, HP-UX, Linux, AIX, and Windows environments. We are DBAs, speakers, educators, and authors. Our team is continually recognized by Oracle, at national conferences and by leading trade publications. Learn more about our remote DBA, database tuning, and consulting services. Or, call us at 415-344-0500 or 888-648-0500.
Starting in Oracle9i it is possible to compile your PL/SQL stored procedures into native machine code on your database server. At one point Oracle University courseware claimed that natively compiled procedures could run up to ten times faster than interpreted PL/SQL code. Sorry folks—they were wrong. (I believe the claim has been pulled from the courseware by now.)
We will begin this white paper with an overview of the native compilation feature. Then, we'll continue on to discuss experiences on a project where a production application including over 35,000 lines of complex PL/SQL code was natively compiled. We'll talk about the actual performance improvements observed, system stability, and the inevitable unexpected issues that popped up along the way. Every system is different, so we can't state, "Natively compiling your PL/SQL will make it run X percent faster." However, we can look at what happened on one system and get a general feel for what might be possible.
When you create a PL/SQL program unit (a stored procedure, function, package, type, or trigger) in an Oracle8i database, Oracle saves both the source code and a compiled byte code representation in the data dictionary. Parsing, validation, and generation of byte codes all occur at object creation time. When you execute the program unit, the PL/SQL interpreter in the database server merely reads the byte codes and performs the specified operations. This avoids parsing, syntax checking, and various other steps at runtime.
Starting in Oracle9i, you have the option to natively compile PL/SQL code instead of generating byte codes for the interpreter. When you create a PL/SQL program unit with native compilation enabled, Oracle stores the source code in the data dictionary and does the usual parsing and validation at object creation time. If the code is valid, then Oracle generates a C code source file and calls a C compiler and linker to generate a shared library file for the PL/SQL program unit. When you execute the program unit, Oracle invokes the shared library directly instead of the PL/SQL interpreter.
When you create a PL/SQL program unit or explicitly recompile it, Oracle9i looks at the setting of the plsql_compiler_flags instance parameter in order to decide whether to natively compile the code or generate byte codes for the interpreter. The value of the plsql_compiler_flags parameter gets saved with the PL/SQL program unit so that the code will be compiled the same way in the event of a future implicit recompile.
(As a reminder, PL/SQL is explicitly recompiled with the ALTER...COMPILE statement, such as "ALTER FUNCTION valid_email_address COMPILE;". Implicit recompiles occur when an invalidated PL/SQL program unit is executed or referenced by another program unit undergoing compilation.)
Since the compilation method is selected at the program unit level, you may compile some PL/SQL program units natively while leaving others to run in interpreted mode (although it turns out that doing so can degrade performance). To natively compile PL/SQL, you will need to have a "make" utility, a linker, and a certified C compiler. Also, the shared library files containing the native code will reside on the file system outside the database—you will need to alter your backup and recovery plan, database cloning scripts, and so on.
How to Compile PL/SQL Natively
In order to natively compile PL/SQL code in an Oracle9i database, you'll need to locate the C compiler, linker, and make utility on the database server, review and possibly edit one make file, create a directory to hold the shared library files, and adjust instance parameter settings. In the following sections we will walk through each of these steps in detail.
Locate Your C Compiler, Linker, and Make Utility
Most operating systems have one standard (or most common) linker and make utility, along with standard locations where they reside. For example, Solaris 8 systems typically have the linker at /usr/ccs/bin/ld and the make utility at /usr/ccs/bin/make. C compilers, however, tend to be less standardized. Depending on your platform, there may be several different C compilers available, and there may not be one standard location where they are installed.
Check the release notes for your platform to determine which C compiler is certified for use with native PL/SQL compilation. (Note that certifications vary by platform.) For example, GNU gcc 2.95.3 is certified for use with Oracle on Linux but not on other Unix platforms. Typically, Oracle certifies native PL/SQL compilation with the same C compilers certified for use with the Pro*C/C++ product. Metalink bulletin 43208.1 lists certified C compilers for all platforms.
Review and Edit the Supplied Make File
Oracle uses the make utility and a make file to compile and link C code into shared libraries. A sample make file is provided which may work for you straight out of the box. The file is called spnc_makefile.mk and is located in the plsql directory under the Oracle home. Review it carefully and edit as required. Of particular concern are the settings for the LD, CC, and CFLAGS variables—the location of the linker, location of the C compiler, and command-line options for the C compiler, respectively.
If you plan to use an uncertified C compiler, you'll definitely need to change the settings for the CC and CFLAGS variables. If you are using a 64-bit version of Oracle, make sure your C compiler generates 64-bit object code. It is interesting to point out that the GNU gcc compiler is not certified by Oracle for use on the Solaris platform, but the supplied make file includes all of the correct settings—commented out—for gcc. As we'll discuss later, we used gcc on our project and uncommenting the appropriate lines in the make file worked fine for us.
Create a Directory for the Shared Library Files
By default, Oracle will store all shared library files in one directory of your choosing. You must create this directory manually, and you should give only the Oracle software owner write permission to the directory. If there are multiple Oracle instances running on one database server, each should have its own directory. In a Unix environment you can create the directory with commands like:
mkdir $ORACLE_HOME/plsql_libs_$ORACLE_SID chown oracle:dba $ORACLE_HOME/plsql_libs_$ORACLE_SID chmod 755 $ORACLE_HOME/plsql_libs_$ORACLE_SID
Each PL/SQL program unit will get its own shared library file. If you have more than about 15,000 PL/SQL program units, Oracle recommends that you configure Oracle to distribute the shared libraries among multiple subdirectories. (File system access can slow down if one directory has too many files in it.) If you do configure the native PL/SQL compilation facility to distribute the shared libraries among multiple subdirectories, you will need to manually create the subdirectories with commands like:
cd $ORACLE_HOME/plsql_libs_$ORACLE_SID mkdir d0 d1 d2 d3 d4 d5... chown oracle:dba d0 d1 d2 d3 d4 d5... chmod 755 d0 d1 d2 d3 d4 d5...
Create as many subdirectories as you like, but they must be named d0, d1, d2, d3, and so on.
Set Instance Parameters
The final step to configuring the database for native PL/SQL compilation is to set four instance parameters as follows:
|plsql_native_make_utility||Set to the full path of the make utility on the database server|
|plsql_native_make_file_name||Set to the full path of the make file|
|plsql_native_library_dir||Set to the full path of the directory where shared library files will be stored|
|plsql_native_library_subdir_count||Set to 0 (the default) in order to store all shared library files in one directory |
or N to cause Oracle to distribute shared library files among N subdirectories
A DBA can dynamically alter these four parameters at the instance level, so an instance restart is not required. The parameters cannot be altered at the session level for security reasons. The first three parameters have null defaults and must be set before PL/SQL code can be compiled natively. Do not use environment variables in the parameter values. Two additional instance parameters—plsql_native_c_compiler and plsql_native_linker—have null defaults and should not be set.
Enable Native PL/SQL Compilation and Explicitly Recompile Code
Whenever you create a PL/SQL program unit or explicitly recompile an existing one, Oracle looks at the setting of the plsql_compiler_flags instance parameter to determine whether native compilation or byte code compilation is desired. This parameter defaults to ‘interpreted,’ but can be changed to ‘native’ at the instance or session level. Typically, you would set this parameter to ‘native’ at the instance level to activate native compilation for all users.
To initially test the native PL/SQL configuration, you might want to change plsql_compiler_flags to ‘native’ in your session only and create a simple “hello world” stored procedure. You should perform this initial test while logged onto the database via SQL*Plus on the database server itself without using Net8. When connected to the database this way, the make session will output any errors to your screen. If you log onto the database via a network connection, error output from the make session will be lost. (This is a known bug still present in Oracle release 188.8.131.52.)
The make file provided on some platforms, including Solaris, tries to save a copy of the existing shared library before compiling and linking a new one. This has an irritating side effect when you natively compile a PL/SQL object for the first time, as there will be no existing copy of the shared library and, therefore, the mv command will fail and display an error.
If the PL/SQL code successfully compiles natively, you will see a shared library file in the directory designated by the plsql_native_library_dir instance parameter. On Unix systems, the file will have a name of the form <object_name>__<schema_name>__N.so, as in DBRX_UTIL__DBRX_OWNER__0.so. You can check the data dictionary to confirm a PL/SQL program unit has been compiled natively with a query like this:
SQL> SELECT object_name, param_name, param_value 2 FROM user_stored_settings 3 WHERE param_name LIKE 'plsql%' 4 AND object_name IN ('LOADER', 'DBRX_UTIL'); OBJECT_NAME PARAM_NAME PARAM_VALUE ----------- -------------------- --------------------- LOADER plsql_compiler_flags INTERPRETED,NON_DEBUG DBRX_UTIL plsql_compiler_flags NATIVE,NON_DEBUG SQL>
Once you have verified that the configuration is correct, you may change the plsql_compiler_flags instance parameter to ‘native’ at the instance level and explicitly recompile all PL/SQL program units in order to natively compile all PL/SQL code. A script is available on the Oracle Technology Network (OTN) to assist with recompiling all PL/SQL objects natively. You can read about the script and download it at http://otn.oracle.com//tech/pl_sql/htdocs/README_2188517.htm.
If you use the Database Creation Assistant to create a new database, you can configure the instance parameters for native PL/SQL compilation before the database is created. By doing so, all of the built-in packages will be natively compiled during database creation so that you don’t need to explicitly recompile each one later.
Benefits of Native PL/SQL Compilation
By default Oracle compiles PL/SQL program units into byte codes that are interpreted at runtime. Why should you consider going to the trouble of configuring, testing, and implementing native PL/SQL compilation? The primary reason is to improve runtime speed. Secondarily, it is conceivable that native PL/SQL compilation could help increase scalability as well.
Natively compiled shared library code is executed directly by a processor in the database server. You would therefore expect natively compiled PL/SQL to run faster than traditional PL/SQL byte codes interpreted by the database server at runtime. Oracle University courseware claimed natively compiled PL/SQL could run up to ten times faster than interpreted, although a manager in the PL/SQL group at Oracle labeled this claim “a bug” in an email to me. Other Oracle documentation claims 30% performance gains are possible. Remember that SQL statements inside a PL/SQL program unit are processed by the SQL engine, and therefore whether the PL/SQL code surrounding the SQL is natively compiled or not will have little impact on how fast the SQL itself runs. Native PL/SQL compilation has the biggest performance impact on computationally intensive PL/SQL code (as opposed to SQL statements). In a later section of this paper, we will look at actual performance gains observed in PL/SQL program units with various blends of PL/SQL code and SQL statements.
It is possible that native PL/SQL compilation can improve application scalability as well. Interpreted PL/SQL requires that all byte codes for an entire PL/SQL program unit be loaded into the shared pool. Natively compiled PL/SQL, on the other hand, uses PGA memory instead. So natively compiled PL/SQL will likely put less demand on the shared pool. However, the shared libraries themselves are loaded into memory by the operating system—so memory is still used, just in different forms.
There are not very many documented limitations of the native PL/SQL compilation facility, and we did not discover any outright restrictions other than those documented. A very minor restriction worth noting is that the PL/SQL compiler debugging feature is not supported when compiling PL/SQL code natively. You can set the plsql_compiler_flags instance parameter to ‘interpreted,debug’ to include debugging in the byte code compilation, but you cannot use ‘native,debug.’
Another restriction that is more common sense than a limitation is that a package body must be compiled the same way as its package specification. That is, if a package specification is compiled natively, then the corresponding package body must also.
One significant limitation—only if you use Real Application Clusters—is that in a RAC environment the shared library files must be stored on a “genuine shared file system.” The requirements and limitations here will vary by platform. According to the release notes, native PL/SQL compilation is not supported at all for RAC systems running on Solaris.
Real Life Experience Using the Native PL/SQL Compilation Facility
In 2002, we considered natively compiling a complex PL/SQL application in order to make it run faster. We set up a test environment that was a full copy of production and ran a battery of tests to assess the ease of use, stability, reliability, and performance benefits of native PL/SQL compilation. In the following sections we will recount our observations in each of these areas.
The test environment consisted of a Sun E450 running Oracle9i Standard Edition 64-bit on Solaris 8. We used the GNU gcc compiler version 2.95.3, although gcc is not certified on Solaris for use with native PL/SQL compilation. The original tests performed in 2002 used Oracle release 184.108.40.206. In late 2003, we repeated the tests using Oracle release 220.127.116.11 to see what had changed in the intervening patch sets. Aside from a few additional pages of documentation and a nifty new demo, not much had changed. In the text below, we’ll point out when our observations varied between patches.
The application itself contained over 35,000 lines of PL/SQL code in 210 packages, procedures, and triggers. The application had backend and frontend modules—all written in PL/SQL. The backend modules included daemons that parse, validate, and load data from flat files into a database schema, daemons that analyze data in the database schema, and daemons that send email messages based on the analysis results. The frontend modules included numerous PL/SQL procedures that generate dynamic web pages using Oracle’s modplsql facility.
Ease of Use
We found the native PL/SQL compilation facility very easy to use. Edit a make file, set a few instance parameters, and configuration is complete. Once configured, native PL/SQL compilation is typically transparent to the developer. There are, however, a few rough edges that could be refined in order to make the facility easier to use. Oracle release 18.104.22.168 and earlier have very little documentation on the topic of native PL/SQL compilation. Release 22.214.171.124 and later, however, do include a few additional pages of information in the platform-specific release notes. Also, Metalink bulletin 151224.1 and the OTN posting at http://otn.oracle.com//tech/pl_sql/htdocs/README_2188517.htm are very helpful.
A few important points are unfortunately not made clear in the documentation. If you have multiple Oracle instances running from the same Oracle home, you should give each a separate directory for storing shared library files. Managing shared library files when two or more instances share the same directory could be problematic and difficult to keep in order. Also, you should treat native PL/SQL compilation as an all-or-nothing proposition: Either natively compile everything (including all of the PL/SQL built-ins such as SYS.STANDARD) or do nothing. Performance degradation occurs when a natively compiled program unit calls an interpreted one, making execution speed even slower than if all code had been interpreted.
Compiler compatibility can be a bit tricky as well. It is unfortunate that Oracle only certifies one or two compilers per platform, and the widely available and free GNU gcc compiler is only certified on the Linux platform. We found it was easier to get native PL/SQL compilation to work on Solaris with the uncertified GNU gcc compiler than it was with the supported Sun Forte compiler. Whether you choose to work with a supported compiler or not, remember that the compiler must generate code for the same architecture (32-bit or 64-bit) as that used by Oracle. You might also want to experiment with the compiler optimization flags specified in the make file in order to find the best balance between compile speed and runtime speed.
As mentioned earlier, the make file provided by Oracle attempts to rename an existing shared library by the same name before compiling a new shared library. The intent appears to be to back up an existing shared library file before it gets overwritten. Unfortunately, this step in the make file causes an unfriendly error message the first time you compile a PL/SQL program unit natively: Since no shared library file by the same name already exists, the mv command fails. This generates output like this:
SQL> ALTER PROCEDURE login COMPILE; mv: cannot access /u01/app/oracle/product/9.2.0/plsql_libs_dbrxprod/ LOGIN__DBRX_OWNER__0.so *** Error code 2 (ignored) The following command caused the error: mv /u01/app/oracle/product/9.2.0/plsql_libs_dbrxprod/LOGIN__DBRX_OWN ER__0.so /u01/app/oracle/product/9.2.0/plsql_libs_dbrxprod/LOGIN__DB RX_OWNER__0.so.$$ Procedure altered. SQL>
The make session ignores the error and goes on to compile the PL/SQL program unit successfully. Remember, as we discussed earlier, error messages from the make session only appear on your display if you run SQL*Plus on the database server itself without using a network connection. These inappropriate error messages don’t interfere with native PL/SQL compilation—they just make the facility look less solid than it really is.
As previously mentioned, error messages generated during a make session are displayed only when connected to the database via a connection that does not use networking. To be precise, on Unix platforms the make session error messages are written to standard error output. So even if you do run SQL*Plus on the database server, make session error messages will appear on the screen but not in a spool file if you were spooling output.
Since meaningful errors during the make session typically only occur if the native PL/SQL compilation facility is not configured correctly, the inconsistent way in which Oracle handles the errors from the make session should not be a big deal. When first configuring native PL/SQL compilation, you should use SQL*Plus directly on the database server. After you have native PL/SQL compilation running smoothly, there likely won’t be any errors in make sessions that you would care about.
Some aspects of how Oracle manages the shared library files leave a lot to be desired, and it is unfortunate that Oracle has not addressed any of these shortcomings as of release 126.96.36.199. When you drop a natively compiled PL/SQL program unit, Oracle does not remove the shared library file. Furthermore, every time a natively compiled PL/SQL program unit gets recompiled (whether explicitly or implicitly) Oracle saves the old copy of the shared library file by adding the Unix PID to its file name. These two facts lead to a buildup of unnecessary files in the shared library directory. These obsolete files will need to be manually deleted.
If you accidentally delete a shared library that is not obsolete (or if you clone a database and forget to copy the shared libraries to the target environment), Oracle will give a less-than-friendly error message the next time the corresponding PL/SQL program unit gets called. Oracle will not attempt to implicitly recompile the program unit, and the status of the program unit in the data dictionary will continue to show as ‘VALID’ even though the program unit cannot be run. When this happens, you’ll need to locate the missing shared library file or explicitly recompile the PL/SQL program unit in order to get it working again. In this area, Oracle seems a lot less forgiving than it could be—it seems like Oracle could simply do an implicit recompile if the shared library could not be found.
Finally in the area of ease of use, it should be pointed out that compiling PL/SQL natively takes longer than generating traditional byte codes. Native compilation speed will vary depending on the compiler optimization settings specified in the make file. We found native compilation takes roughly ten times as long as byte code compilation. Of course, PL/SQL code does not need to be compiled very frequently, and so the slower compile time should not be a significant issue.
Stability and Reliability
We found the native PL/SQL compilation facility to be extremely stable and reliable. Once the make file and instance parameters were configured correctly, we found that the runtime experience for natively compiled PL/SQL was just as solid as for interpreted PL/SQL. We experienced no ORA-00600 or “PL/SQL internal error” messages at runtime. Computationally intensive code appeared to give the same numeric results whether compiled natively or interpreted.
There are a few known bugs documented on Metalink, but the serious ones appear to have all been resolved by release 188.8.131.52. Particularly on the Windows platform, early releases of Oracle9i had problems with syntax errors in the supplied make file, missing make files, unresolved symbols at compile time, and stack overflows at runtime. On Unix, RMAN and the import utility would fail in certain circumstances when native PL/SQL compilation was enabled. Again, these problems have been fixed by release 184.108.40.206.
As of this writing, the main outstanding bugs have to do with handling of the error output from the make session and the handling of shared library files when objects are dropped or recompiled. Neither of these are serious problems.
Observed Performance Gains
We ran a series of performance tests, both with trivial code and a production application, in order to get a feel for how native PL/SQL compilation can improve runtime performance. In this section, we’ll share our observations. However, please keep in mind that performance can vary dramatically based on hardware, operating system, Oracle release, application code, and many other factors. Please take the information stated here as a starting point only, and run your own tests to determine how native PL/SQL compilation performs in your environment.
That said, we found that the ratio between time spent executing PL/SQL versus SQL in a PL/SQL program unit had a strong influence over how much impact native compilation would have. Since native compilation causes PL/SQL statements to run faster but does nothing to boost SQL statement speed, this should be intuitively obvious. So a program unit that executes entirely PL/SQL statements with no SQL would be expected to run faster when natively compiled, while a program unit that primarily executes SQL statements would not be expected to gain much from native compilation.
We found that a stored procedure that does nothing more than iterate through an empty loop 100,000,000 times took about 67% less time to run when natively compiled. This is a terrific performance gain, but one you are not likely to see in a real application. At the opposite extreme, we found that a stored procedure that fetches from the SYS.dual table 100,000 times ran only about 1% faster when natively compiled. This demonstrates that native PL/SQL compilation doesn’t make SQL run any faster (but it can shave microseconds off of the parse, bind, execute, and fetch calls).
Most real application code will fall somewhere between the two extremes listed above. A stored procedure that added 100,000 numbers together in a loop took roughly 25% less time to run when natively compiled. A stored procedure that computed and added 100,000 cosines together in a loop did not appear to run any faster when natively compiled. This suggests that Oracle’s cosine routines are already natively compiled, so natively compiling the PL/SQL that calls them adds imperceptible benefit.
Next we measured performance gains on two large PL/SQL program units in the production application. The first module was a data loader package containing parsing and validation logic consisting primarily of PL/SQL statements and load logic that consisted primarily of SQL. The package overall was heavy on PL/SQL and light on SQL. It took about 12% less time to run when natively compiled.
The second module was a dynamic web page that queried data from the database and generated HTML output. This stored procedure was much more laden with SQL and only had a little bit of procedural logic in between the queries. We observed no measurable performance gain when this program unit was natively compiled.
We experimented with the C compiler optimization settings as well. Of course, this will vary greatly from one compiler to the next. The GNU gcc compiler has a -O option for setting optimization level. We found that the runtime performance gains offered by the maximum optimization level were very minimal. However, using maximum optimization doubled compilation times over the basic optimization.
As we mentioned before, we did all of our performance tests in 2002 using Oracle release 220.127.116.11 64-bit on Solaris 8. We repeated the tests in 2003 using Oracle release 18.104.22.168 64-bit on Solaris 8 and found no significant difference in performance. The 22.214.171.124 release does include a native PL/SQL compilation demo, and we ran the demo for fun.
The demo is located in the plsql/demo directory under the Oracle home in a script called “ncmpdemo.sql”. This demo lists the basic steps to configuring native PL/SQL compilation, natively compiles and runs a simple “Hello World” stored procedure, and then creates a computationally intensive stored procedure that finds right-angled triangles with integer lengths on each side. The demo runs the triangle procedure both natively compiled and interpreted, so that you can compare the runtimes and see the benefit of native PL/SQL compilation.
On our system, the demo took about 20% less time to run when natively compiled. This is a respectable performance gain, but—like the procedure that iterates through an empty loop 100,000,000 times—it is not a gain you are likely to see frequently in a real production system. (The demo code contained no SQL statements.) It was interesting to note that the demo script on the Solaris platform sets the plsql_native_make_utility instance parameter to “gmake”. This goes against the documentation stipulation that the full path must be specified, and it also uses the GNU make utility instead of the standard /usr/ccs/bin/make utility included in Solaris.
We evaluated native PL/SQL compilation not just to try out a new Oracle9i feature, but because we had a legitimate complex PL/SQL application that we wanted to make run faster if there was an easy way to do so. We found the native PL/SQL compilation facility in Oracle9i to be easy enough to set up and quite stable, but the performance gains were not substantial enough in our particular situation to be worthwhile. In the end, we left our production system on the traditional interpreted PL/SQL setup.
Many minor glitches give the impression that native PL/SQL compilation might be an immature and unstable feature at this point—inappropriate error messages that appear the first time you natively compile a program unit, make session error messages that get lost if you access the database from a network connection, a supplied make file (on Solaris, anyhow) that includes comments showing how to configure an unsupported compiler, and the very fact that the supplied demo does not set instance parameters in accordance with the documentation.
The aforementioned issues aside, the native PL/SQL compilation feature in Oracle9i does seem to be pretty stable and worthy of production use. If you have a lot of procedural logic in your application and wish it would run faster, native compilation is certainly worth investigating and testing.
Oracle documentation, Metalink, and OTN all offer additional information on the topic of native PL/SQL compilation. Here are some resources you might find helpful:
About the Author
Roger Schrag, OCP, has been an Oracle DBA and application architect for over 14 years. He started at Oracle Corporation on the Oracle Financials development team. Subsequent positions in the San Francisco Bay Area included DBA and database architect. In 1995, Roger founded Database Specialists, Inc. Roger is a frequent speaker at OracleWorld and the International Oracle Users Group (IOUG) Live conferences, where he has been voted Top 10% of speakers multiple times. Also, Roger was an Oracle Masters Class instructor for the IOUG. Roger is the current president of the Northern California Oracle Users Group (NoCOUG). He can be reached at email@example.com.
Still Looking for Help on this Subject? Get a Consultation
We would be happy to talk with you about our services and how our senior-level database team might help you. Call Database Specialists at 415-344-0500 or 888-648-0500 or fill out a free consultation request form.
If you'd like to receive our complimentary monthly newsletter with database tips and new white paper announcements, sign up for The Specialist.
Copyright © 2005 Database Specialists, Inc. http://www.dbspecialists.com