Oracle PL/SQL Built For SpeedAugust 7, 2003 Everyone is looking for an edge. With PL/SQL that edge is compiling the PL/SQL code into native code. This article will show you how to setup and compile your PL/SQL procedures and packages. The default behavior for creation of PL/SQL code execution is purely interpretive. If you want to skip the interpreted aspect of PL/SQL and jump through to native C code, then you must use a C compiler on the system. When you do this Oracle generates native C code, then compiles it with the C compiler on your system, turns it into shared libraries, and then it is linked into the Oracle process. This means that when these procedures are called they are immediately available for execution. All of this is dynamic and you do not need to restart the database to take advantage of this feature. Just remember, the only speed increase you will achieve is PL/SQL code execution, not SQL execution. Therefore, if your PL/SQL code is laced with SQL statements and not logical statements, the switch to C code will not buy you much speed. Setup to Compile1. Within the $ORACLE_HOME/plsql directory, there is a makefile called spnc_makefile.mk. You will need to change this file for the appropriate paths for the C compiler. A quick look at this file and a few verifications proved un-eventful as Oracle had all the paths set for my Linux installation. Table 1 will show the paths I verified and what command I used to verify them. TABLE 1:
2. The initialization parameter PLSQL_COMPILER_FLAGS must be set to NATIVE. The default value for this parameter is INTERPRETED. You can verify your current setting by issuing the 'SHOW PARAMETER plsql_compiler_flags' command. You can change the setting by issuing an ALTER SESSION or ALTER SYSTEM command. Listing A shows my session to verify my current setting and set this parameter to NATIVE. LISTING A: SQL> connect sys/<password> as sysdba Connected. SQL> show parameter plsql_compiler_flags NAME TYPE VALUE ------------------------------------ ----------- ------------------------- plsql_compiler_flags string INTERPRETED SQL> ALTER SYSTEM SET plsql_compiler_flags = NATIVE SCOPE = both; System altered. SQL> show parameter plsql_compiler_flags NAME TYPE VALUE ------------------------------------ ----------- ------------------------- plsql_compiler_flags string NATIVE 3. Listing B gives more parameters that you must set to control the compiling of PL/SQL. Read through them and take appropriate action according to your environment. Each of these parameters may be set through the ALTER SYSTEM or ALTER SESSION commands. Listing C gives my session for setting these parameters. LISTING B:
LISTING C: SQL> connect sys/<password> as sysdba
Connected.
SQL> show parameter native
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_native_c_compiler string
plsql_native_library_dir string
plsql_native_library_subdir_count integer 0
plsql_native_linker string
plsql_native_make_file_name string
plsql_native_make_utility string
SQL> alter system set PLSQL_NATIVE_LIBRARY_DIR =
'/u01/app/oracle/product/9.2/plsql/lib' scope=both;
System altered.
SQL> alter system set PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 500 scope=both;
System altered.
SQL> alter system set PLSQL_NATIVE_MAKE_FILE_NAME =
'/u01/app/oracle/product/9.2/plsql/spnc_makefile.mk' scope=both;
System altered.
SQL> alter system set PLSQL_NATIVE_MAKE_UTILITY = '/usr/bin/gmake' scope=both;
System altered.
SQL> show parameter native
NAME TYPE VALUE
--------------------------------- ------ ------------------------------
plsql_native_c_compiler string
plsql_native_library_dir string /u01/app/oracle/product/9.2/plsql/lib
plsql_native_library_subdir_count integer 0
plsql_native_linker string
plsql_native_make_file_name string /u01/app/oracle/product/9.2/plsql/spnc_makefile.mk
plsql_native_make_utility string /usr/bin/gmake
|
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
$(ORACLE_HOME)/plsql/