Oracle DBA Tips Corner

     Return to the Oracle DBA Tips Corner.

click me  

Native Compiled PL/SQL

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Introduction
  2. Initialization Parameters Related to PL/SQL Native Compilation
  3. An Example PL/SQL Native Compilation Session
  4. Why is the Execution of Native Code Much Faster?
  5. Verifying that a Procedure is being Executed Natively in Solaris
  6. Dependencies, Invalidation and Revalidation


Introduction

In versions prior to Oracle9i, all PL/SQL stored program units are compiled to P-code. The resulting P-code is stored in the database and interpreted at runtime. While this results in a very portable solution, it does have some performance consequences; interpreted languages are usually slower than natively compiled languages.

As a performance enhancement, Oracle9i provides the capability to natively compile PL/SQL code into shared libraries. These shared libraries are then loaded and executed whenever the corresponding PL/SQL program unit is invoked at runtime.

Native compilation of PL/SQL, once it has been properly configured, is transparent to both the user compiling a PL/SQL program unit and to the end user invoking that same PL/SQL program unit.

Oracle9i generates C code corresponding to the PL/SQL code and then compiles and links the resulting C code into shared libraries using the compiler and linker for that platform. Therefore, a C compiler must be installed on the same host for native compilation to work.

You can compile both the packages supplied by Oracle and the procedures or packages that you custom develop. Oracle supplied packages are statically linked, and procedures or packages you develop are dynamically linked to the process running the Oracle executable.

The initialization parameters that are used to configure PL/SQL native compilation are described next.


Initialization Parameters Related to PL/SQL Native Compilation


An Example PL/SQL Native Compilation Session

To compile procedures and packages, you must perform the following steps:

  1. Update the Oracle-supplied makefile spnc_makefile.mk located in $ORACLE_HOME/plsql to reflect the appropriate paths:

    (In most cases, no modifications will need to be made to this file. All directories should be correct as a result of the install.)

  2. Replace the default INTERPRETED value for the PLSQL_COMPILER_FLAGS initialization parameter with the NATIVE value. You can perform this by modifying the entry in the initialization parameter file, or you can use ALTER SESSION or ALTER SYSTEM to dynamically alter the PLSQL_COMPILER_FLAGS parameter, as shown below: 
      SQL> SELECT name, value FROM v$parameter where name = 'plsql_compiler_flags';

      NAME                   VALUE
      ---------------------  ---------------
      plsql_compiler_flags   INTERPRETED

  SQL> ALTER SYSTEM SET PLSQL_COMPILER_FLAGS = NATIVE;

  SQL> SELECT name, value FROM v$parameter where name = 'plsql_compiler_flags';

      NAME                   VALUE
      ---------------------  ---------------
      plsql_compiler_flags   NATIVE

  3. Set all other plsql_% instance parameters. The the section above, Initialization Parameters Related to PL/SQL Native Compilation In general, here is a list of the parameters you will need to change:

    • plsql_compiler_flags
    • plsql_native_make_file_name
    • plsql_native_make_utility
    • plsql_native_library_dir

    From SQL*Plus, you can use the following command to ensure all values have been properly set: 

      SQL> show parameters plsql

  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------
  plsql_compiler_flags                 string      NATIVE
  plsql_native_c_compiler              string
  plsql_native_library_dir             string      /u01/app/oracle/product/9.2.0/plsql_libs
  plsql_native_library_subdir_count    integer     0
  plsql_native_linker                  string
  plsql_native_make_file_name          string      /u01/app/oracle/product/9.2.0/plsql/spnc_makefile.mk
  plsql_native_make_utility            string      /usr/bin/make
  plsql_v2_compatibility               boolean     FALSE

  4. Now, compile the procedure or the package of interest with one of the following:

    • Use CREATE OR REPLACE PROCEDURE to compile the procedure.
    • Use the ALTER PROCEDURE command to compile the procedure.
    • Use the ALTER PACKAGE command to compile the entire package.
    • Use DROP PROCEDURE to drop the procedure and then create it again with the CREATE procedure to compile the procedure.
    • Create a database with the PLSQL_COMPILER_FLAGS initialization parameter set to NATIVE to compile ALL the Oracle-supplied packages. Oracle does this by starting the UTLIRP script in: $ORACLE_HOME/rdbms/admin/utlirp.sql.

      (Keep in mind that the utlirp.sql script can be used to invalidate and then recompile all PL/SQL modules (procedures, functions, packages, types, triggers, views) in a database.)

  5. Confirm that the procedure of interest has been NATIVE compiled by querying the USER_STORED_SETTINGS data dictionary view: 
      SELECT object_name, param_value
  FROM  user_stored_settings
  WHERE object_name = 'FILL_EMP'
    AND param_name  = 'plsql_compiler_flags';


  OBJECT_NAME      PARAM_VALUE
  ---------------  ------------------------
  FILL_EMP         NATIVE,NON_DEBUG
    NOTE: If the procedure FILL_EMP is not compiled for native execution, it will contain INTERPRETED within the param_value column.


Why is the Execution of Native Code Much Faster?

Besides the standard degradation in performance to parse interpreted code, the execution of native code is much faster because of the following:

Source: OCP Oracle9i Database: New Features for Administrators Exam Guide (Exam 1Z0-030), Daniel Benjamin


Verifying that a Procedure is being Executed Natively in Solaris

On Solaris, one can verify that the procedure is being executed natively by using 'pmap' to view the libraries loaded by the oracle executable. Use the following methods:


Dependencies, Invalidation and Revalidation

Dependencies between database objects are handled in the same manner as in previous Oracle versions. If an object upon which some natively compiled PL/SQL program unit depends changes, the PL/SQL module is invalidated. The next time the same PL/SQL program unit is executed, an attempt is made to revalidate (recompile) the module. Any time a module is recompiled as part of revalidation, it is compiled using its stored setting (i.e. the setting in force the last time the module was explicitly compiled and appearing in the USER/ALL/DBA_STORED_SETTINGS data dictionary views).

The stored settings are ONLY used when recompiling as part of revalidation. Any time a PL/SQL module is explicitly compiled via 'create or replace' or 'alter ... compile', the current setting for the session is used.

Although natively compiled PL/SQL program units are obviously dependent on their implementation shared libraries, the database is unable to track such dependencies (since the libraries, which reside on the file system, can be manipulated directly via OS commands). For example, attempting to execute a PL/SQL program unit whose shared library has been deleted results in error. 

  $ rm /u01/app/oracle/product/9.2.0/plsql_libs/FILL_EMP__SCOTT__0.so

  $ sqlplus scott/tiger

  SQL> execute fill_emp(100);

  *
  ERROR at line 1:
  ORA-06549: PL/SQL: failed to dynamically open shared object (DLL):
  /u01/app/oracle/product/9.2.0/plsql_libs/FILL_EMP__SCOTT__0.so: cannot open
  shared object file: No such file or directory

Since the removal of the library is undetectable to Oracle until the module is executed, the program unit is not marked INVALID; therefore, there is no implicit revalidation (and recompilation). To recreate the missing library, you must explicitly recompile it or recreate it from source. 

  SQL> ALTER SESSION SET plsql_compiler_flags='NATIVE';

  Session altered.

  SQL> ALTER PROCEDURE fill_emp COMPILE;

  Procedure altered.

  SQL> exit

  $ ls -l /u01/app/oracle/product/9.2.0/plsql_libs

  -rwxr-xr-x    1 oracle   dba         12081 Mar 19 18:39 FILL_EMP__SCOTT__0.so*

On a related note, shared libararies corresponding to a PL/SQL program unit are not automatically deleted when the program unit is dropped; these files must be manually removed when they are no longer needed.


Copyright (c) 1998-2010 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Friday, 20-Jan-2006 16:47:48 EST
Page Count: 22537