Compiling PL/SQL Code for an Oracle Database

In this third part of a nine-part series on managing PL/SQL code, you’ll learn how to compile PL/SQL source code, how to use the optimizing compiler, and more. It is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O’Reilly; ISBN: 0596009771). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Perform One-Time DBA Setup

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:

  1. Get a supported C compiler.
  2. Set up directory(ies) in the filesystem that will hold the natively compiled files.
  3. Check $ORACLE_HOME/plsql/spnc_commands.

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).

Table 20-1. Sampling of C compilers required by native compilation

   Oracle Database
Platform version(s) Supported C compiler(s)
Sun Sparc Solaris 9.2 Sun Forte Workshop 6.2 (with particular patches from Sun); spnc_command.mk includes gcc-specific comments, but GCC doesn’t appear to be officially supported
  10.1 Sun ONE Studio 8, C/C++ 5.5
  10.2 Same as above, plus GCC 3.4
Microsoft Windows 9.2 Microsoft Visual C++ 6.0
2000, XP, 2003 10.1 Microsoft Visual C++ 6.0;  Microsoft Visual C++ .NET 2002;  Microsoft Visual C++ .NET 2003
  10.2 Same as above, plus MinGW GCC 3.2.3a
Linux Intel 32bit 9.2 GNU GCC 2.95.3
  10.1 Red Hat Linux 2.1: GNU GCC 2.96.108.1
  10.1  Red Hat Linux 3: GNU GCC 3.2.3-2
  10.1 UnitedLinux 1.0: GNU GCC 3.2.2-38
  10.1 Vendor-independent: Intel C++ 7.1.0.28
  10.2 Red Hat: GCC 3.2.3-34
  10.2 Suse: GCC 3.3.3-43
  10.2 Vendor-independent: Intel C++ Compiler v7.1.0.28

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) :
  $ mkdir
/u01/app/oracle/oracle/product/10.2.0/ db_1/dbs/ncomps

  $ sqlplus "/ as sysdba"
  …
  SQL> ALTER SYSTEM SET plsql_native_library_dir =
    2  ‘/u01/app/oracle/oracle/product/ 10.2.0/db_1/dbs/ncomps';

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 OF F
  SPOOL makedirs.sh
  BEGIN
   
FOR dirno IN 0..999
   
LOOP
     
DBMS_OUTPUT.PUT_LINE(‘mkdir d’ || dirno || ‘ && echo ‘ || dirno);
   
END LOOP;
  END;
  /
  SPOOL OFF

Then, edit out the cruft at the top and bottom of the script, and at the operating sys tem prompt, do something like this:

  $ cd /u01/app/oracle/oracle/product/ 10.2.0/db_1/dbs/ncomps
  $
sh makedirs.sh

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

With native compilation in Oracle9i Database, the setup is slightly different, and there are several limitations that you’ll want to keep in mind:

  1. In addition to getting a supported C compiler, setting the directory parameters, and inspecting the spnc_makefile.mk as discussed above, there are extra parameters required at the system or session level: PLSQL_NATIVE_MAKE_UTILITY (typically make) and PLSQL_NATIVE_MAKE_FILE_NAME (the fully qualified path to spnc_makefile.mk).
  2. While a CREATE or REPLACE of a module replaces the existing DLL, a DROP of a module does not delete the DLL from the filesystem. You will need to remove these files manually.
  3. There is no storage of the content of the object file in the data dictionary as in Oracle Database 10g. You definitely want to adjust your system-level backup to include the native file directories.
  4. Package specifications and package bodies must match in compilation style (both interpreted, or both native). In Oracle Database 10g, they don’t have to match.

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.

{mospagebreak title=Interpreted Versus Native Compilation Mode}

After your administrator sets everything up, you are ready to go native. The first thing to do is set the compiler parameter. A user may set it as described here.

In Oracle9i Database, specify:

  ALTER SESSIO N
    SET plsql_compiler_flags = ‘NATIVE';  /* vs. ‘INTERPRETED’ */

Starting with Oracle Database 10g Release 1, the PLSQL_COMPILER_FLAGS parameter is deprecated, so you should use this instead:

  ALTER SESSION
     SET plsql_code_type = ‘NATIVE';   /* vs. ‘INTERPRETED’ */

The compilation mode will then be set for subsequently compiled PL/SQL library units during that session, as long as they are compiled by one of the following:

  1. A script or explicit CREATE [OR REPLACE] command
  2. An ALTER… COMPILE statement
  3. The DBMS_UTILITY.COMPILE_SCHEMA packaged procedure

In addition, the DBA can change the mode on a system-wide basis using ALTER SYSTEM.

Oracle stores the compilation mode with the library unit’s metadata so that if the program is implicitly recompiled as a consequence of dependency checking, the last mode used will be used again. Note that this “stickiness” applies only to automatic recompilations; other rebuilds or recompiles will use the session’s current setting. You can determine the saved compilation mode for your stored programs by
query ing the data dictionary using the statement shown here (for Oracle Database 10g):

  SELECT name, type, plsql_code_type
   
FROM USER_PLSQL_OBJECT_SETTINGS
   
ORDER BY name;

The result will show something like this:  

NAME

TYPE PLSQL_CODE_TYPE

———————–

——- ———— ———————

ANIMAL_HIST_TRG

TRIGGER NATIVE

DEMO

PACKAGE BODY INTERPRETED

DEMO

PACKAGE INTERPRETED

ORDER_SEEDS

PROCEDURE NATIVE

PLVTMR

PACKAGE NATIVE

PLVTMR

PACKAGE BODY NATIVE

PRINTANY

FUNCTION INTERPRETED

In Oracle9i Database, the WHERE clause would instead look for PLSQL_COMPILER_FLAGS, and you would get additional information about whether the unit has been compiled with debug mode.

Incidentally, PL/SQL debuggers will not work with natively compiled programs. This is one of the only downsides to native compilation, but in most cases you could work around it by using interpreted mode during development, and native mode in test ing and production.

Oracle recommends that all of the PL/SQL library units called from a given top-level unit be compiled in the same mode (see the sidebar “Converting an Entire Database to Native (or Interpreted)”). That’s because there is a cost for the context switch when a library unit compiled in one mode invokes one compiled in the other mode. Significantly, this recommendation includes the Oracle-supplied library units. These are always shipped compiled in interpreted mode because they may need to get recompiled during subsequent upgrades, and Oracle cannot assume that you have installed a supported C compiler.

Our conclusion? If your application contains a significant amount of compute-intensive logic, consider switching your entire database—including Oracle’s supplied library units—to use native compilation. Making such a change is likely to offer the most dramatic performance improvements for applications that are unable to take advantage of the optimizing compiler introduced in Oracle Database 10g.


Converting an Entire Database to Native (or Interpreted)

The simplest way to follow Oracle’s recommendation that all PL/SQL library units called from a given top-level unit be compiled in the same mode is to convert the whole database so that all PL/SQL library units are compiled native, and to set the system-wide parameter PLSQL_COMPILER_FLAGS (Oracle9i Database) or PLSQL_CODE_TYPE (Oracle Database 10g) to NATIVE.

While this is not technically difficult to do, it can be very time-consuming if you have a large number of modules. There are a number of nonobvious steps to the process; we suggest following Oracle’s explicit instructions closely.

  1. For Oracle9i Database: http://otn.oracle.com/tech/pl_sql/htdocs/README_ 2188517.htm
  2. For Oracle Database 10g: http://www.oracle.com/technology/tech/pl_sql/ htdocs/ncomp_faq.html#ncomping_db

The latter link is actually part of a larger FAQ that contains a wealth of useful information on native compilation.


{mospagebreak title=Using the Optimizing Compiler and Compile-Time Warnings}

You don’t have to make any changes to your code to take advantage of two of the most important enhancements to Oracle Database 10g PL/SQL: the optimizing compiler and compile-time warnings.

The Optimizing Compiler

PL/SQL’s optimizing compiler can improve runtime performance dramatically, with a relatively slight cost at compile time. The benefits of optimization apply to both interpreted and natively compiled PL/SQL because optimizations are applied by analyzing patterns in source code.

The optimizing compiler is enabled by default. However, you may want to alter its behavior, either by lowering its aggressiveness or by disabling it entirely. For example, if, in the course of normal operations, your system must perform recompilation of many lines of code, or if an application generates many lines of dynamically executed PL/SQL, the overhead of optimization may be unacceptable. Keep in mind, though, that Oracle’s tests show that the optimizer doubles the runtime performance of computationally intensive PL/SQL.

In some cases, the optimizer may even alter program behavior. One such case might occur in code written for Oracle9i Database that depends on the relative timing of initialization sections in multiple packages. If your testing demonstrates such a problem, yet you wish to enjoy the performance benefits of the optimizer, you may want to rewrite the offending code or to introduce an initialization routine that ensures the desired order of execution.

The optimizer settings are defined through the PLSQL_OPTIMIZE_LEVEL initialization parameter (and related ALTER DDL statements), which can be set to 0, 1, or 2. The higher the number, the more aggressive is the optimization, meaning that the compiler will make a greater effort, and possibly restructure more of your code to optimize performance.

Set your optimization level according to the best fit for your application or program, as follows:

PLSQL_OPTIMIZE_LEVEL = 0

Zero essentially turns off optimization. The PL/SQL compiler maintains the original evaluation order of statement processing of Oracle9i Database and earlier releases. Your code will still run faster than in earlier versions, but the difference will not be so dramatic.

PLSQL_OPTIMIZE_LEVEL = 1

The compiler will apply many optimizations to your code, such as eliminating unnecessary computations and exceptions. It will not, in general, change the order of your original source code.

PLSQL_OPTIMIZE_LEVEL = 2

This is the default value and the most aggressive setting. It will apply many modern optimization techniques beyond level 1, and some of those changes may result in moving source code relatively far from its original location. Level 2 optimization offers the greatest boost in performance. It may, however, cause the compilation time in some of your programs to increase substantially. If you encounter this situation (or, alternatively, if you are developing your code and want to minimize compile time, knowing that when you move to production, you will apply the highest optimization level), try cutting back the optimization level to 1.

You can set the optimization level for the instance as a whole, but then override the default for a session or for a particular program. Here are some examples:

  ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

Oracle retains optimizer settings on a module-by-module basis. When you recompile a particular module with nondefault settings, the settings will “stick,” allowing you to recompile later using REUSE SETTINGS. For example:

  ALTER PROCEDURE bigproc COMPILE PLSQL_OPTIMIZE_LEVEL = 0;

and then:

  ALTER PROCEDURE bigproc COMPILE REUSE SETTINGS;

To view all the compiler settings for your modules, including optimizer level, interpreted versus native, and compiler warning levels, query the
USER_PLSQL_OBJECT_SETTINGS view.

For lots more information on the optimizing compiler, see Chapter 23 and visit:

  http://www.oracle.com/technology/tech/pl_sql/htdocs/ new_in_10gr1.htm#faster

Compile-Time Warnings

Compile-time warnings can greatly improve the maintainability of your code and reduce the chance that bugs will creep into it. Compile-time warnings differ from compile-time errors; with warnings, your program will still compile and run. You may, however, encounter unexpected behavior or reduced performance as a result of running code that is flagged with warnings.

This section explores how compile-time warnings work and which issues are currently detected. Let’s start with a quick example of applying compile-time warnings in your session.

A quick example

A very useful compile-time warning is PLW-06002: Unreachable code. Consider the following program (available in the cantgothere.sql file on the book’s web site). Because I have initialized the salary variable to 10,000, the conditional statement will always send me to line 9. Line 7 will never be executed.

       /* File on web: cantgothere.sql * /
  1  CREATE OR REPLACE PROCEDURE cant_go_there
  2  AS
  3     l_salary NUMBER := 10000;
  4  BEGIN
  5      IF l_salary > 20000
  6      THEN
  7         DBMS_OUTPUT.put_line (‘Executive’);
  8     ELSE
  9       DBMS_OUTPUT.put_line (‘Rest of Us’);
 10     END IF;
  11  * END cant_go_there;

If I compile this code in any release prior to Oracle Database 10g Release 1, I am sim ply told “Procedure created.” If, however, I have enabled compile-time warnings in my session on the new release and then try to compile the procedure, I get this response from the compiler:

  SP2-0804: Procedure created with compilation warnings

  SQL> sho err
 
Errors for PROCEDURE CANT_GO_THERE:

  LINE/COL ERROR 
  ——– ———————————
 
7/7      PLW-06002: Unreachable code

Given this warning, I can now go back to that line of code, determine why it is unreachable, and make the appropriate corrections.

If you see ano message file message

If you are running 10.1.0.2.0 on Windows, and try to reproduce what I showed in the previous section, you will see this message:

  7/7     PLW-06002: Message 6002 not found;
            No message file for 
          product=plsql, facility=PLW

The problem is that Oracle didn’t ship the message file, plwus.msb, with the Ora cle Database 10g software until 10.1.0.3.0, and the download available on OTN is 10.1.0.2.0. If you encounter this problem, you will need to contact Oracle Support to obtain this file (reference Bug 3680132) and place it in the plsqlmesg subdirectory. You will then be able to see the actual warning message.

Verify your SQL*Plus version

If you are running a pre-Oracle Database 10g version of SQL*Plus, it will not be able to display warnings; because Oracle9i Database did not support compile-time warnings, commands like SHOW ERRORS don’t even try to obtain warning information.

Specifically, the ALL_ERRORS family of data dictionary views has two new columns in Oracle Database 10g: ATTRIBUTE and MESSAGE_NUMBER. The earlier SQL*Plus versions don’t know how to interpret these columns.

To determine if you are using a pre-Oracle Database 10g version of SQL*Plus, execute these commands in SQL*Plus:

  CREATE TABLE t (n BINARY_FLOAT )
  /
  DESCRIBE t

In such versions of SQL*Plus, you will see that “n” is characterized as “UNDE FINED.” Starting with Oracle Database 10g Release 1, SQL*Plus will properly show the type of this column to be “BINARY_FLOAT.”

{mospagebreak title=How to turn on compile-time warnings}

Oracle allows you to turn compile-time warnings on and off, and also to specify the type of warnings that interest you. There are three categories of warnings:

Severe
  
Conditions that could cause unexpected behavior or
   actual wrong results, such as aliasing problems with
   parameters

Performance
  
Conditions that could cause performance problems,
   such as passing a VARCHAR2 value to a NUMBER
   column in an UPDATE statement

Informational
  
Conditions that do not affect performance or
   correctness, but that you might want to change to
   make the code more maintainable

Oracle lets you enable/disable compile-time warnings for a specific category, for all categories, and even for specific, individual warnings. You can do this with either the ALTER DDL command or the DBMS_WARNING built-in package.

To turn on compile-time warnings in your system as a whole, issue this command:

  ALTER SYSTEM SET PLSQL_WARNINGS=’string’

The following command, for example, turns on compile-time warnings in your system for all categories:

  ALTER SYSTEM SET PLSQL_WARNINGS=’ENABLE:ALL';

This is a useful setting to have in place during development because it will catch the largest number of potential issues in your code.

To turn on compile-time warnings in your session for severe problems only, issue this command:

  ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:SEVERE';

And if you want to alter compile-time warnings settings for a particular, already-compiled program, you can issue a command like this:

  ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS=’ENABLE:ALL’ REUSE SETTINGS;

Make sure to include REUSE SETTINGS to make sure that all other settings (such as the optimization level) are not affected by the ALTER command.

You can tweak your settings with a very high level of granularity by combining different options. For example, suppose that I want to see all performance-related issues, that I will not concern myself with server issues for the moment, and that I would like the compiler to treat PLW-05005: function exited without a RETURN as a compile error. I would then issue this command:

  ALTER SESSION SET PLSQL_WARNINGS=
   
‘DISABLE:SEVERE’
  
,’ENABLE:PERFORMANCE’
  
,’ERROR:05005′;

I especially like this “treat as error” option. Consider the PLW-05005: function returns without value warning. If I leave PLW-05005 simply as a warning, then when I compile my no_return function, shown below, the program does compile, and I can use it in my application.

  SQL> CREATE OR REPLACE FUNCTION no_return
    2     RETURN VARCHAR2
    3  AS
    4  BEGIN
    5     DBMS_OUTPUT.PUT_LINE (
    6      
 ‘Here I am, here I stay’);
    7
  END no_return;
    8  /

  SP2-0806: Function created with compilation warnings

  SQL> sho err
 
Errors for FUNCTION NO_RETURN:
 

  LINE/COL ERROR 
  ——– ———————————
  1/1      PLW-05005: function NO_RETURN
           returns without value at line 7

If I now alter the treatment of that error with the ALTER SESSION command shown above and then recompile no_return, the compiler stops me in my tracks:

  Warning: Procedure altered with compilation errors

By the way, I could also change the settings for that particular program only, to flag this warning as a “hard” error with a command like this:

  ALTER PROCEDURE no_return COMPILE PLSQL_WARNINGS = ‘error:6002′ REUSE SETTINGS
  /

This ability to treat a warning as an error did not work in 10.1.0.2; this program was fixed in Oracle Database 10g Release 2 and is reported to be back-ported to 10.1.0.3.

You can, in each of these variations of the ALTER command, also specify ALL as a quick and easy way to refer to all compile-time warnings categories, as in:

  ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL';

Oracle also provides the DBMS_WARNING package, which provides the same capabilities to set and change compile-time warning settings through a PL/SQL API. DBMS_WARNING also goes beyond the ALTER command, allowing you to make changes to those warning controls that you care about while leaving all the others intact. You can also easily restore the original settings when you’re done.

DBMS_WARNING was designed to be used in install scripts in which you might need to disable a certain warning, or treat a warning as an error, for individual pro gram units being compiled. You might not have any control over the scripts surrounding those for which you are responsible. Each script’s author should be able to set the warning settings he wants, while inheriting a broader set of settings from a more global scope.

Please check back next week for the continuation of this article.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri