Native PL/SQL Compilation in Oracle9i

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

2. Today’s 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 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.

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 oracle:dba $ORACLE_HOME/plsql_libs_$ORACLE_SID 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 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.

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 can’t 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/README_2188517.htm

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

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

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

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

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

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 SQL*Plus. SHOW ERRORS displays “PLS-00923: native compilation failed: make:spdtexmk:?”.

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

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.