native pl sql compilation in oracle9i
Skip this Video
Download Presentation
Native PL/SQL Compilation in Oracle9i

Loading in 2 Seconds...

play fullscreen
1 / 61

Native PL - PowerPoint PPT Presentation

  • Uploaded on

Native PL/SQL Compilation in Oracle9i. Roger Schrag Database Specialists, Inc. Today’s Topics. Overview of PL/SQL native compilation What is it? How do you use it? Why? Documented limitations. More Topics. My experience with PL/SQL native compilation

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Native PL' - nida

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
native pl sql compilation in oracle9i
Native PL/SQL Compilationin Oracle9i

Roger Schrag

Database Specialists, Inc.

today s topics
Today’s Topics

Overview of PL/SQL native compilation

What is it?

How do you use it?


Documented limitations

more topics
More Topics

My experience with PL/SQL native compilation

Project background

Ease of use

Stability and reliability


Overall impressions

feature overview
Feature Overview

What is “PL/SQL native compilation”?

How do you use this feature?

Why would you want to bother?

Are there any documented limitations?

pl sql native compilation
PL/SQL Native Compilation

Starting in Oracle9i Release 1, PL/SQL program units can be compiled directly into machine code.

Stored procedures, functions, packages, types, and triggers

Alternatively, PL/SQL code can be interpreted as in Oracle8i and earlier.

when pl sql native compilation occurs
When PL/SQL Native Compilation Occurs

When you create or explicitly recompile a PL/SQL program, the plsql_compiler_flags instance parameter tells Oracle whether or not to natively compile the code.

This setting gets saved with the PL/SQL program and used in the future in the event of an implicit recompile.

explicit vs implicit compilation
Explicit vs. Implicit Compilation

Explicit compilation is where you tell Oracle to compile a program unit:



ALTER FUNCTION valid_email_address COMPILE;

Implicit compilation is where Oracle needs to access a PL/SQL program unit that has been invalidated. In this case Oracle recompiles the program without being told to do so.

how pl sql code is compiled
How PL/SQL Code is Compiled

When you compile PL/SQL into byte codes, Oracle parses the code, validates it, and gen- erates byte codes for interpretation at runtime.

If plsql_compiler_flags is set to ‘native’, then Oracle generates a C code source file instead of the byte codes. The C code is compiled using your C compiler, and linked into a shared library callable by the oracle executable.

how to natively compile pl sql programs
How to Natively Compile PL/SQL Programs

Locate C compiler, linker, and make utility.

Edit the supplied make file as needed.

Create a shared library directory.

Set instance parameters.

Explicitly compile PL/SQL programs.

Query the data dictionary.

locate your c compiler linker and make utility
Locate Your C Compiler, Linker, and Make Utility

Oracle uses these tools on your database server to natively compile PL/SQL programs.

See the Oracle Release Notes for your platform or Metalink bulletin #43208.1 for which C compiler is certified for use with Pro*C on your platform.

Most operating systems come with one standard linker and make utility.

Find where these utilities are installed on your database server.

edit the supplied make file
Edit the Supplied Make File

Oracle provides a make file called in $ORACLE_HOME/plsql. Verify the variable settings:

CC: Location of C compiler

LD: Location of linker

CFLAGS: C compiler optimization settings

You might not need to make any changes to the make file if you are using the C compiler certified by Oracle.

create a shared library directory
Create a Shared Library Directory

All compiled shared libraries will reside here.

Use a separate directory for each database.

Only the Oracle software owner should have write privilege to this directory.


mkdir $ORACLE_HOME/plsql_libs_$ORACLE_SID

chown oracle:dba $ORACLE_HOME/plsql_libs_$ORACLE_SID

chmod 755 $ORACLE_HOME/plsql_libs_$ORACLE_SID

set instance parameters
Set Instance Parameters

Set at instance level:





Turn native compilation on and off at the instance or session level:


plsql native make utility

Specifies the full path of the make utility on the database server.

Default value is null.

Must set to natively compile PL/SQL.

A DBA can dynamically alter this setting at the instance level.

Users cannot alter at the session level.

plsql native make file name

Specifies the full path of the make file.

Default value is null.

Must set to natively compile PL/SQL.

A DBA can dynamically alter this setting at the instance level.

Users cannot alter at the session level.

plsql native library dir

Specifies the full path of the directory where shared libraries will be stored.

Directory must exist—Oracle won’t create it.

Default value is null.

Must set to natively compile PL/SQL.

A DBA can dynamically alter this setting at the instance level.

Users cannot alter at the session level.

plsql native library subdir count

Specifies the number of subdirectories to be used under plsql_native_library_dir.

Default value is 0.

Set this to a value greater than zero if you expect to have 15,000 or more natively compiled PL/SQL programs. (Filesystem performance degrades if you have too many files in one directory.)

A DBA can dynamically alter this setting at the instance level.

Users can’t alter at the session level.

plsql compiler flags

Specifies whether or not PL/SQL programs should be natively compiled, and whether or not debugging code should be generated.

Default value is ‘interpreted’. Alternate values are ‘native’, ‘debug’, and ‘non_debug’.

A DBA can dynamically alter this setting at the instance level.

Users can also alter this setting at the session level.

plsql native c compiler and plsql native linker
plsql_native_c_compiler and plsql_native_linker

Specifies the full path of the C compiler and linker on the database server.

Default value is null.

You should leave these parameters unset and allow the make file to specify the locations.

A DBA can dynamically alter these settings at the instance level.

Users cannot alter at the session level.

sample parameter settings
Sample Parameter Settings

ALTER SYSTEM SET plsql_native_make_utility =


ALTER SYSTEM SET plsql_native_make_file_name =


ALTER SYSTEM SET plsql_native_library_dir =


ALTER SYSTEM SET plsql_compiler_flags = \'native\';

explicitly compile pl sql programs
Explicitly Compile PL/SQL Programs



Implicitly recompiled PL/SQL will be recompiled the way it was originally compiled. The setting of plsql_compiler_flags is ignored during an implicit recompile.

Script to assist with explicitly recompiling all code:

query the data dictionary
Query the Data Dictionary

SQL> SELECT object_name, param_name,

2 param_value

3 FROM user_stored_settings

4 WHERE param_name LIKE \'plsql%\'

5 AND object_name IN (\'LOADER\', \'DBRX_UTIL\')

6 /


----------- -------------------- ---------------------

DBRX_UTIL plsql_compiler_flags NATIVE,NON_DEBUG


why compile pl sql programs for native execution
Why Compile PL/SQL Programs For Native Execution?

Boost performance.

Improve scalability.

boosting performance
Boosting Performance

Procedural logic like IF/THEN, loops, and jumps bypass Oracle’s PL/SQL byte code interpreter.

SQL statements within a PL/SQL program are not affected.

Oracle University course material claims natively compiled “PL/SQL without SQL references is 2 to 10 times faster” than interpreted code.

improving scalability
Improving Scalability

Byte codes for interpreted PL/SQL programs are loaded into the shared pool in their entirety at invocation.

Natively compiled PL/SQL programs use PGA memory, reducing shared pool contention. (Of course, the shared libraries still have to be loaded into memory by the operating system.)

documented limitations
Documented Limitations

Package bodies must be compiled the same way as their specifications—either both are interpreted or both are natively compiled.

The debugging facility is not available in PL/SQL programs compiled for native execution.

Not a limitation: You are allowed to natively compile the built-in PL/SQL packages.

my experience with pl sql native compilation
My Experience with PL/SQL Native Compilation

Project background

Ease of use

Stability and reliability


Overall impressions

database rx testbed for pl sql native compilation
Database Rx: Testbed For PL/SQL Native Compilation

Application we use at Database Specialists to monitor our customers’ databases.

Daemons receive message files from agents running on customers’ servers at regular intervals.

Message files are parsed, loaded into database, and analyzed for trends and problems.

Reports are generated and emailed automatically.

Users can generate ad hoc reports via web.

98% of application written in PL/SQL.

test environment basic stats
Test Environment Basic Stats

Oracle9i Release 2 ( and 64 bit Standard Edition.

Sun E450 server running 64 bit Solaris 8.

Schema contains 168 tables.

Over 35,000 lines of PL/SQL in 210 program units (packages, procedures, triggers).

Backend PL/SQL programs parse, load, and analyze message files.

Frontend PL/SQL programs generate reports and dynamic web pages.

ease of use
Ease of Use

On paper, PL/SQL native compilation looks easy to use:

Setup requires just a few ALTER SYSTEM commands.

Once set up, native compilation is transparent to the developer.

In reality it is pretty easy to use, but does have a few rough edges.

ease of use issues
Ease of Use Issues


Compiler compatibility

Compiler and make file issues

Error handling

Compile speed

Managing shared library files

All or nothing approach


Release and earlier provided little documentation, but is a bit better:

See the platform-specific release notes

Metalink bulletin #151224.1 is helpful

Oracle Technology Network posting also helpful:


Important points not mentioned in the documentation:

Use a separate shared library directory for each database

Natively compile everything or nothing

compiler compatibility
Compiler Compatibility

PL/SQL native compilation is only certified with one or two C compilers on each platform.

Certifications for Oracle9i Release 2:

Solaris 64 bit: Sun Forte Workshop 6.2 (eight patches required)

Solaris 32 bit: Sun Forte Workshop 6.1 or 6.2

HP-UX: HP ANSI C B.11.01.25171 (one patch required)

Linux: gcc 2.95.3

compiler issues
Compiler Issues

Make file provided with 64 bit Oracle for Solaris is designed for use with Sun Forte Workshop 6.2, but I couldn’t get it to work with that compiler.

Comments in make file show changes required to use gcc instead of Forte.

I uncommented the lines for gcc and it worked with gcc 3.1 perfectly the first time.

more compiler issues
More Compiler Issues

Note that if you are using 64 bit Oracle, then your compiler must generate code for 64 bit architecture.

For gcc this means adding “-m64” to CFLAGS.

Odd error at runtime: “wrong ELF class: ELFCLASS32”

Test optimization flags to find ideal performance vs. compile speed balance.

make file issues
Make File Issues

Compiling a PL/SQL program native the first time will give output in SQL*Plus like the following:

SQL> ALTER SESSION SET plsql_compiler_flags = \'NATIVE\';

Session altered.


mv: cannot access /u01/app/oracle/product/9.2.0/plsql_libs


*** Error code 2 (ignored)

The following command caused the error:

mv /u01/app/oracle/product/9.2.0/plsql_libs_dbrxprod/LOGIN /u01/app/oracle/product/9.2.0/plsql_lib


Procedure altered.


error handling during native compilation
Error Handling During Native Compilation

Error output from make session will write to your screen when connected to a local database.

Error output is lost when connected to a remote database via Oracle Net.

These error messages are not accessible on the Oracle error stack, the user_errors view, or the SHOW ERRORS command in SQL*Plus.

SHOW ERRORS displays “PLS-00923: native compilation failed: make:spdtexmk:?”.

compile speed
Compile Speed

Compiling PL/SQL for native execution is much slower than compiling for interpreted execution (anywhere from twice as long to ten times as long).

Compilation speed depends on C compiler and linker speed.

Speed is strongly influenced by C compiler optimization settings.

managing shared library files
Managing Shared Library Files

When you drop a natively compiled PL/SQL program, Oracle does not delete the shared library file.

When a natively compiled PL/SQL program gets recompiled (explicitly or implicitly), Oracle renames the old shared library file and does not delete it.

It is up to the DBA to manually delete obsolete shared library files.

missing shared library files
Missing Shared Library Files

Oracle will give an error if it cannot find a shared library file.

Oracle will not create a new shared library automatically.

Data dictionary will show PL/SQL is valid.

You must explicitly recompile the PL/SQL.

So DBAs must be very careful...

...when purging obsolete shared library files

...when cloning databases

all or nothing
All or Nothing

Natively compile all PL/SQL programs or none of them.

Performance penalty occurs when natively compiled code calls interpreted code.

The result can be slower than if all code was interpreted.

Applies to built-ins (like SYS.STANDARD) too.

all or nothing44
All or Nothing

The documentation does not mention this anywhere.

The 1000+ built-ins are not natively compiled by default when you create a database. Recompiling all of the built-ins for native execution takes time.

Oracle has provided a script on OTN that will recompile all PL/SQL for native execution.

has all pl sql been natively compiled
Has All PL/SQL BeenNatively Compiled?

SQL> SELECT param_value, COUNT(*)

2 FROM dba_stored_settings

3 WHERE param_name = \'plsql_compiler_flags\'

4 GROUP BY param_value;


--------------------- ----------



stability and reliability
Stability and Reliability

Once a PL/SQL program unit has been successfully compiled for native execution, it seems just as solid to me at runtime as if it were being interpreted.

Computationally intensive code gives precisely the same results whether natively compiled or interpreted.

I experienced no ORA-00600 errors or weird “PL/SQL internal error” messages.

performance tests
Performance Tests

Null loop

Basic arithmetic


Select from dual

Database Rx file loader

Database Rx report viewer

Compiler optimization flags

Oracle9i vs. Oracle8i

performance test null loop
Performance Test: Null Loop

Iterate through an empty loop 100,000,000 times.

Compile Method CPU Seconds

Interpreted 67.40

Native 21.62

Runtime savings:67%

Conclusion: Branching and no-ops run significantly faster when natively compiled.

performance test basic arithmetic
Performance Test: Basic Arithmetic

Add 10,000,000 numbers together.

Compile Method CPU Seconds

Interpreted 20.65

Native 14.99

Runtime savings:27%

Conclusion: Basic arithmetic runs faster when natively compiled.

performance test cosines
Performance Test: Cosines

Compute 100,000 cosines and add them together.

Compile Method CPU Seconds

Interpreted 28.40

Native 28.25

Runtime savings: Less than 1%

Conclusion: Native compilation cannot speed up certain mathematical computations.

performance test select from dual
Performance Test: Select From Dual

Fetch one row from SYS.dual 100,000 times.

Compile Method CPU Seconds

Interpreted 24.88

Native 24.47

Runtime savings:2%

Conclusion: Native compilation cannot speed up SQL, but it seems to reduce SQL processing overhead very slightly.

performance test database rx file loader
Performance Test: Database Rx File Loader

Parse, validate, and load 8700 text messages.

Compile Method CPU Seconds

Interpreted 17.79

Native 15.68

Runtime savings:12%

Conclusion:Complex PL/SQL code containing some SQL and a lot of procedural logic can run somewhat faster when natively compiled.

performance test database rx report viewer
Performance Test: Database Rx Report Viewer

Generate four dynamic web pages, including an eight page Performance Summary report

Compile Method CPU Seconds

Interpreted 9.64

Native 9.66

Runtime savings:None.

Conclusion: PL/SQL programs containing resource-heavy SQL and only a little procedural logic do not benefit from native compilation.

performance test compiler optimization flags
Performance Test: Compiler Optimization Flags

Add 10,000,000 numbers together.

Compile Method CPU Seconds

Interpreted 20.65

Native (no optimization) 15.40

Native (gcc –O1) 14.63

Native (gcc –O3) 14.99

Conclusion: The C compiler optimization flags can impact the speed at which natively compiled PL/SQL programs run.

performance test compiler optimization flags55
Performance Test:Compiler Optimization Flags

Compile Database Rx loader package (5700 lines).

Compile Method Seconds to Compile

Interpreted 4.79

Native (no optimization) 46.74

Native (gcc –O1) 89.80

Native (gcc –O3) 180.53

Conclusion: C compiler optimization flags have a substantial impact on how long it takes to compile a PL/SQL program for native execution.

performance test oracle9i versus oracle8i
Performance Test: Oracle9i versus Oracle8i

Parse, validate, and load 8700 text messages.

Compile Method CPU Seconds

Interpreted (9i) 17.79

Native (9i) 15.68

Interpreted (8i) 21.85

Conclusion: Complex PL/SQL containing a lot of procedural logic can run 20% faster in Oracle9i than in Oracle8i—even without native compilation. (The savings here were in procedural logic execution, not SQL optimization.)

pl sql native compilation my overall impressions
PL/SQL Native Compilation: My Overall Impressions

On the plus side:

Can speed up some PL/SQL programs

Extremely stable (, on Solaris)

Relatively bug-free

Not too hard to set up has improved documentation over previous releases

pl sql native compilation my overall impressions58
PL/SQL Native Compilation: My Overall Impressions

On the minus side:

Performance gains are extremely modest

The natively compiled code consists of a series of calls to Oracle’s PL/SQL state machine and nothing more.

Shared library management is an accident waiting to happen.

Documentation in earlier releases is lacking

caveat your mileage may vary
Caveat: Your Mileage May Vary!

Performance gains vary from one application to the next.

Stability varies from one environment to the next.

Never take somebody else’s word on performance or stability.

Always test on your system using your platform, your application, and your version of Oracle.

additional resources
Additional Resources

Platform-specific Release Notes

Note covering the basics: Metalink bulletin #151224.1

List of certified C compilers: Metalink bulletin #43208.1

White paper that accompanies this presentation:

Script to switch database between native and interpreted (recompiles all PL/SQL):

White papers on OTN that discuss new features in PL/SQL, including native compilation:

contact information
Contact Information

Roger Schrag

Database Specialists, Inc.

388 Market Street, Suite 400

San Francisco, CA 94111

Tel: 415/344-0500

Email: [email protected]