Native PL/SQL Compilation in Oracle9i

1
Native PL/SQL Compilationin Oracle9i
Roger Schrag Database Specialists,
Inc. www.dbspecialists.com
2
Todays Topics

• Overview of PL/SQL native compilation
• What is it?
• How do you use it?
• Why?
• Documented limitations

3
More Topics

• My experience with PL/SQL native compilation
• Project background
• Ease of use
• Stability and reliability
• Performance
• Overall impressions

4
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?

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

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

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

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

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

10
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 ProC on your platform.
  
• Most operating systems come with one standard
  linker and make utility.
• Find where these utilities are installed on your
  database server.

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

12
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 oracledba ORACLE_HOME/plsql_libs_ORACLE_S
  ID
• chmod 755 ORACLE_HOME/plsql_libs_ORACLE_SID

13
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

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

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

16
plsql_native_library_dir

• Specifies the full path of the directory where
  shared libraries will be stored.
• Directory must existOracle wont 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.

17
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 cant alter at the session level.

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

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

20
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'

21
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/RE
  ADME_2188517.htm

22
Query the Data Dictionary

• SQLgt 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

23
Why Compile PL/SQL Programs For Native Execution?

• Boost performance.
• Improve scalability.

24
Boosting Performance

• Procedural logic like IF/THEN, loops, and jumps
  bypass Oracles 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.

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

26
Documented Limitations

• Package bodies must be compiled the same way as
  their specificationseither 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.

27
My Experience with PL/SQL Native Compilation

• Project background
• Ease of use
• Stability and reliability
• Performance
• Overall impressions

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

29
Database Rx Testbed For PL/SQL Native Compilation
30
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.

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

32
Ease of Use Issues

• Documentation
• Compiler compatibility
• Compiler and make file issues
• Error handling
• Compile speed
• Managing shared library files
• All or nothing approach

33
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_2
  188517.htm

34
Documentation

• Important points not mentioned in the
  documentation
• Use a separate shared library directory for each
  database
• Natively compile everything or nothing

35
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

36
Compiler Issues

• Make file provided with 64 bit Oracle for Solaris
  is designed for use with Sun Forte Workshop 6.2,
  but I couldnt 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.

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

38
Make File Issues

• Compiling a PL/SQL program native the first time
  will give output in SQLPlus like the following
• SQLgt ALTER SESSION SET plsql_compiler_flags
  'NATIVE'
• Session altered.
• SQLgt ALTER PROCEDURE login COMPILE
• mv cannot access /u01/app/oracle/product/9.2.0/pl
  sql_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_dbrxpr
  od/LOGIN
• __DBRX_OWNER__0.so /u01/app/oracle/product/9.2.0/p
  lsql_lib
• s_dbrxprod/LOGIN__DBRX_OWNER__0.so.
• Procedure altered.
• SQLgt

39
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 SQLPlus.
• SHOW ERRORS displays PLS-00923 native
  compilation failed makespdtexmk?.

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

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

42
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

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

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

45
Has All PL/SQL BeenNatively Compiled?

• SQLgt 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

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

47
Performance Tests

• Null loop
• Basic arithmetic
• Cosines
• Select from dual
• Database Rx file loader
• Database Rx report viewer
• Compiler optimization flags
• Oracle9i vs. Oracle8i

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

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

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

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

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

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

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

55
Performance TestCompiler 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.

56
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 Oracle8ieven without native compilation.
  (The savings here were in procedural logic
  execution, not SQL optimization.)

57
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

58
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 Oracles PL/SQL state machine and
  nothing more.
• Shared library management is an accident waiting
  to happen.
• Documentation in earlier releases is lacking

59
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 elses word on performance or
  stability.
• Always test on your system using your platform,
  your application, and your version of Oracle.

60
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_21
  88517.htm
• White papers on OTN that discuss new features in
  PL/SQL, including native compilation
  http//otn.oracle.com/tech/pl_sql

61
Contact Information

• Roger Schrag
• Database Specialists, Inc.
• 388 Market Street, Suite 400
• San Francisco, CA 94111
• Tel [phone]
• Email rschrag_at_dbspecialists.com
• Web www.dbspecialists.com