Home arrow Oracle arrow Page 2 - Compiling PL/SQL Code for an Oracle Database

Interpreted Versus Native Compilation Mode - Oracle

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.

TABLE OF CONTENTS:
  1. Compiling PL/SQL Code for an Oracle Database
  2. Interpreted Versus Native Compilation Mode
  3. Using the Optimizing Compiler and Compile-Time Warnings
  4. How to turn on compile-time warnings
By: O'Reilly Media
Rating: starstarstarstarstar / 9
November 01, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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 SESSION
    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
querying 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 testing 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.




 
 
>>> More Oracle Articles          >>> More By O'Reilly Media
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: