Native pl sql compilation in oracle9i l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 61

Native PL/SQL Compilation in Oracle9i PowerPoint PPT Presentation


  • 137 Views
  • Uploaded on
  • Presentation posted in: General

Native PL/SQL Compilation in Oracle9i. Roger Schrag Database Specialists, Inc. www.dbspecialists.com. 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

Download Presentation

Native PL/SQL Compilation in Oracle9i

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 l.jpg

Native PL/SQL Compilationin Oracle9i

Roger Schrag

Database Specialists, Inc.

www.dbspecialists.com


Today s topics l.jpg

Today’s Topics

Overview of PL/SQL native compilation

What is it?

How do you use it?

Why?

Documented limitations


More topics l.jpg

More Topics

My experience with PL/SQL native compilation

Project background

Ease of use

Stability and reliability

Performance

Overall impressions


Feature overview l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Explicit vs. Implicit Compilation

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

CREATE PACKAGE dbrx_util…

CREATE OR REPLACE TRIGGER customers_t1…

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Edit the Supplied Make File

Oracle provides a make file called spnc_makefile.mk 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 l.jpg

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.

Example:

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 l.jpg

Set Instance Parameters

Set at instance level:

plsql_native_make_utility

plsql_native_make_file_name

plsql_native_library_dir

plsql_native_library_subdir_count

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

plsql_compiler_flags


Plsql native make utility l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

Sample Parameter Settings

ALTER SYSTEM SET plsql_native_make_utility =

'/usr/ccs/bin/make';

ALTER SYSTEM SET plsql_native_make_file_name =

'/u01/app/oracle/product/9.2.0/plsql/spnc_makefile.mk';

ALTER SYSTEM SET plsql_native_library_dir =

'/u01/app/oracle/product/9.2.0/plsql_libs_dbrxprod';

ALTER SYSTEM SET plsql_compiler_flags = 'native';


Explicitly compile pl sql programs l.jpg

Explicitly Compile PL/SQL Programs

CREATE OR REPLACE PACKAGE dbrx_util…

ALTER TRIGGER customers_t1 COMPILE;

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: http://otn.oracle.com//tech/pl_sql/htdocs/README_2188517.htm


Query the data dictionary l.jpg

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 /

OBJECT_NAME PARAM_NAME PARAM_VALUE

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

DBRX_UTIL plsql_compiler_flags NATIVE,NON_DEBUG

LOADER plsql_compiler_flags INTERPRETED,NON_DEBUG


Why compile pl sql programs for native execution l.jpg

Why Compile PL/SQL Programs For Native Execution?

Boost performance.

Improve scalability.


Boosting performance l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

My Experience with PL/SQL Native Compilation

Project background

Ease of use

Stability and reliability

Performance

Overall impressions


Database rx testbed for pl sql native compilation l.jpg

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.


Database rx testbed for pl sql native compilation29 l.jpg

Database Rx: Testbed For PL/SQL Native Compilation


Test environment basic stats l.jpg

Test Environment Basic Stats

Oracle9i Release 2 (9.2.0.1 and 9.2.0.4) 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 l.jpg

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 l.jpg

Ease of Use Issues

Documentation

Compiler compatibility

Compiler and make file issues

Error handling

Compile speed

Managing shared library files

All or nothing approach


Documentation l.jpg

Documentation

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

See the platform-specific release notes

Metalink bulletin #151224.1 is helpful

Oracle Technology Network posting also helpful: http://otn.oracle.com//tech/pl_sql/htdocs/README_2188517.htm


Documentation34 l.jpg

Documentation

Important points not mentioned in the documentation:

Use a separate shared library directory for each database

Natively compile everything or nothing


Compiler compatibility l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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.

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_OWNER__0.so /u01/app/oracle/product/9.2.0/plsql_lib

s_dbrxprod/LOGIN__DBRX_OWNER__0.so.$$

Procedure altered.

SQL>


Error handling during native compilation l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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 l.jpg

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;

PARAM_VALUE COUNT(*)

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

INTERPRETED,NON_DEBUG 1349

NATIVE,NON_DEBUG 1


Stability and reliability l.jpg

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 l.jpg

Performance Tests

Null loop

Basic arithmetic

Cosines

Select from dual

Database Rx file loader

Database Rx report viewer

Compiler optimization flags

Oracle9i vs. Oracle8i


Performance test null loop l.jpg

Performance Test: Null Loop

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

Compile MethodCPU 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 l.jpg

Performance Test: Basic Arithmetic

Add 10,000,000 numbers together.

Compile MethodCPU Seconds

Interpreted 20.65

Native 14.99

Runtime savings:27%

Conclusion: Basic arithmetic runs faster when natively compiled.


Performance test cosines l.jpg

Performance Test: Cosines

Compute 100,000 cosines and add them together.

Compile MethodCPU 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 l.jpg

Performance Test: Select From Dual

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

Compile MethodCPU 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 l.jpg

Performance Test: Database Rx File Loader

Parse, validate, and load 8700 text messages.

Compile MethodCPU 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 l.jpg

Performance Test: Database Rx Report Viewer

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

Compile MethodCPU 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 l.jpg

Performance Test: Compiler Optimization Flags

Add 10,000,000 numbers together.

Compile MethodCPU 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 l.jpg

Performance Test:Compiler Optimization Flags

Compile Database Rx loader package (5700 lines).

Compile MethodSeconds 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 l.jpg

Performance Test: Oracle9i versus Oracle8i

Parse, validate, and load 8700 text messages.

Compile MethodCPU 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 l.jpg

PL/SQL Native Compilation: My Overall Impressions

On the plus side:

Can speed up some PL/SQL programs

Extremely stable (9.2.0.1, 9.2.0.4 on Solaris)

Relatively bug-free

Not too hard to set up

9.2.0.4 has improved documentation over previous releases


Pl sql native compilation my overall impressions58 l.jpg

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 l.jpg

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 l.jpg

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:http://www.dbspecialists.com/presentations.html

Script to switch database between native and interpreted (recompiles all PL/SQL): http://otn.oracle.com/tech/pl_sql/htdocs/README_2188517.htm

White papers on OTN that discuss new features in PL/SQL, including native compilation: http://otn.oracle.com/tech/pl_sql


Contact information l.jpg

Contact Information

Roger Schrag

Database Specialists, Inc.

388 Market Street, Suite 400

San Francisco, CA 94111

Tel: 415/344-0500

Email: [email protected]

Web: www.dbspecialists.com


  • Login