1 / 15

Native PL/SQL Compilation

Native PL/SQL Compilation. Julian Dyke Independent Consultant. Web Version. juliandyke.com . Native PL/SQL Compilation. Oracle 9.0.1 and above includes support for native compilation of PL/SQL into a shared library (DLL) in the file system

quana
Download Presentation

Native PL/SQL Compilation

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Native PL/SQL Compilation Julian Dyke Independent Consultant Web Version juliandyke.com

  2. Native PL/SQL Compilation • Oracle 9.0.1 and above includes support for native compilation of PL/SQL into a shared library (DLL) in the file system • A C compiler is required to use native compilation of PL/SQL. This can be a GNU C compiler • New parameters include

  3. Native PL/SQL Compilation • PLSQL_COMPILER_FLAGS • Determines whether PL/SQL code is compiled native or interpreted • Determines whether debug information is included • Can be set at system or session level • Possible values • Defaults are 'INTERPRETED','NON_DEBUG' • Invalid combinations • NATIVE and INTERPRETED • DEBUG and NON_DEBUG • NATIVE and DEBUG

  4. Native PL/SQL Compilation • PLSQL_COMPILER_FLAGS • For example ALTER SESSION SET PLSQL_COMPILER_FLAGS = 'NATIVE'; ALTER SESSION SET PLSQL_COMPILER_FLAGS = 'INTERPRETED', 'DEBUG';

  5. Native PL/SQL Compilation • PLSQL_NATIVE_MAKE_FILE_NAME • Determines full path to makefile used to create shared libraries containing natively compiled PL/SQL code • Set at SYSTEM level • Mandatory if PLSQL_COMPILER_FLAGS = 'NATIVE' • Should specify full pathname of makefile • Sample makefile is • $ORACLE_HOME/plsql/spnc_makefile.mk • No default value - must be set explicitly • For example: ALTER SESSION SET PLSQL_NATIVE_MAKE_FILE_NAME = '/u01/app/oracle/product/9.0.1/plsql/spnc_makefile.mk'

  6. Native PL/SQL Compilation • PLSQL_NATIVE_MAKE_UTILITY • Determines full path to make utility used to process makefile specified by PLSQL_NATIVE_MAKE_FILE_NAME • Set at SYSTEM level • Mandatory if PLSQL_COMPILER_FLAGS = 'NATIVE' • Should specify full pathname of make utility • No default value - must be set explicitly • For example: ALTER SESSION SET PLSQL_NATIVE_MAKE_UTILITY = '/usr/ccs/bin/make';

  7. Native PL/SQL Compilation • PLSQL_NATIVE_LIBRARY_DIR • Determines directory name used to store shared libraries that contains natively compiled PL/SQL code • Set at SYSTEM level • Mandatory if PLSQL_COMPILER_FLAGS = 'NATIVE' • Should be set to full pathname of specified directory • Oracle user must have write permissions on specified directory • Access by other users should be restricted • Directory must be created manually at operating system level • No default value - must be set explicitly • For example: ALTER SESSION SET PLSQL_NATIVE_LIBRARY_DIR = '/usr/app/oracle/product/9.0.1/plsql_libs;

  8. Native PL/SQL Compilation • PLSQL_NATIVE_C_COMPILER • Should not be set • with an ALTER SYSTEM command • in init.ora • in stored parameter file (SPFILE) • Should be set in spnc_makefile.mk • For example: # Specify C Compiler#CC=/opt/SUNWspro/bin/cc

  9. Native PL/SQL Compilation • PLSQL_NATIVE_LINKER • Should not be set • with an ALTER SYSTEM command • in init.ora • in stored parameter file (SPFILE) • Should be set in spnc_makefile.mk • For example: # Specify Linker#LD=/usr/ccs/bin/ld

  10. Native PL/SQL Compilation • PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT • Determines number of subdirectories to be created in directory specified by PLSQL_NATIVE_LIBRARY_DIR • Performance ofsome file operations degrades if there are a large number of files in a directory • Use this parameter to specify number of subdirectories that should be created • Directories should be called d0, d1, d2... dN where N is the value specified for this parameter • Set at SYSTEM level • Default value is 0 - do not use subdirectories

  11. Native PL/SQL Compilation • To compile an interpreted function ALTER SESSION SET PLSQL_COMPILER_FLAGS = ‘INTERPRETED’;ALTER FUNCTION f1 COMPILE; • To compile a native function ALTER SESSION SET PLSQL_COMPILER_FLAGS = ‘NATIVE’;ALTER FUNCTION f2 COMPILE;

  12. Native PL/SQL Compilation • Four tests were performed using the same user-defined function written in different languages • The PL/SQL version of the function was CREATE OR REPLACE FUNCTION double (n NUMBER) RETURN NUMBER IS v_total NUMBER;BEGIN v_total := 0; FOR f IN 1..n LOOP v_total:= v_total + 2; END LOOP; RETURN v_total;END;

  13. Native PL/SQL Compilation • The function was called using the statement SELECT double (5000000) FROM dual; • Results were

  14. Native PL/SQL Compilation • In Oracle 10.1 and above compiled native PL/SQL libraries are stored in BLOBs • Advantages • Libraries can be backed up using RMAN • Libraries compiled on primary database will be automatically propagated to standby

  15. Thank you for your interest For more information and to provide feedback please contact me My e-mail address is: info@juliandyke.com My website address is: www.juliandyke.com

More Related