Why native compilation?
Simple answer is 'for better run-time performance'.
If you do not use native compilation, each PL/SQL program unit is compiled into an intermediate form, machine-readable code (m-code). The m-code is stored in the database dictionary and interpreted at run time.
With PL/SQL native compilation, the PL/SQL statements are turned into C code that bypasses all the runtime interpretation, giving faster run-time performance.
So, just think of it as Java byte code (PL/SQL interpreted mode) Vs Native C or C++ code (PL/SQL natively compiled code). Which one performs better?
Steps for PL/SQL native compilation
If you are trying to convert the database from interpreted compilation to native compilation for PL/SQL packages, following metalink document 312421.1 PL/SQL Native Compilation of Applications 11i on Oracle Database 10g Release 1 (10.1.0), it is very likely that you may end up with the errors described in the bug 5144563 STANDARD package is invalid after upgrade. You will see the following error message when you run $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script as described in step #4 of above metalink document:
This is mainly due to the incorrect order of the instructions posted in metalink document 312421.1. dbmsupgnv.sql script expects a non-NULL value for
For completeness, the steps for the PL/SQL native compilation are as follows:
Compiling PL/SQL objects natively
Reference:
Metalink document 312421.1 PL/SQL Native Compilation of Applications 11i on Oracle Database 10g Release 1 (10.1.0)
______________
Technorati tags:
Oracle | PL/SQL | Performance
Simple answer is 'for better run-time performance'.
If you do not use native compilation, each PL/SQL program unit is compiled into an intermediate form, machine-readable code (m-code). The m-code is stored in the database dictionary and interpreted at run time.
With PL/SQL native compilation, the PL/SQL statements are turned into C code that bypasses all the runtime interpretation, giving faster run-time performance.
So, just think of it as Java byte code (PL/SQL interpreted mode) Vs Native C or C++ code (PL/SQL natively compiled code). Which one performs better?
Steps for PL/SQL native compilation
If you are trying to convert the database from interpreted compilation to native compilation for PL/SQL packages, following metalink document 312421.1 PL/SQL Native Compilation of Applications 11i on Oracle Database 10g Release 1 (10.1.0), it is very likely that you may end up with the errors described in the bug 5144563 STANDARD package is invalid after upgrade. You will see the following error message when you run $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql script as described in step #4 of above metalink document:
ORA-06553: PLS-213: package STANDARD not accessible
This is mainly due to the incorrect order of the instructions posted in metalink document 312421.1. dbmsupgnv.sql script expects a non-NULL value for
plsql_native_library_dir
parameter. So, swapping the 2nd and 3rd instructions would fix the issue.For completeness, the steps for the PL/SQL native compilation are as follows:
Compiling PL/SQL objects natively
- Modify spnc_commands file
Edit$ORACLE_HOME/plsql/spnc_commands
. Make sure to use the absolute path of C compiler. Also feel free to add all compiler options which would improve the run-time performance of the native code.
eg.,
The following line was copied from a Solaris server running 64-bit version of Oracle 10g.% cat $ORACLE_HOME/plsql/spnc_commands
/export/home/oracle/SS11/SUNWspro/bin/cc %(src) -fast -m64 -xcode=pic13 \
-I$(ORACLE_HOME)/plsql/include -I$(ORACLE_HOME)/plsql/public -G -o %(so) - Modify initialization parameters
Append the following parameters to init<SID>.ora file.plsql_optimize_level = 2
plsql_code_type = native
plsql_native_library_dir = <absolute_path_of_any_directory>
plsql_native_library_subdir_count = 150
Make sure to create the directory specified underplsql_native_library_dir
parameter to hold the native PL/SQL objects (*.so). Also remember to create 150 subdirectories with names d0 to d149 under directory pointed byplsql_native_library_dir
. Failure to create those directories may result inPLS-00801
error during compilation. - Shut down and restart the database in upgrade mode
Shut down the database. Restart the database in upgrade mode by using SQL*Plus to connect to the database as SYSDBA and run the following command:% sqlplus / as sysdba
SQL> startup upgrade
Check the value ofplsql_native_library_dir
.SQL> show parameter plsql
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type string native
plsql_compiler_flags string NATIVE, NON_DEBUG
plsql_debug boolean FALSE
plsql_native_library_dir string /opt/oracle/oradata/plsql_nativelib
plsql_native_library_subdir_count integer 150
plsql_optimize_level integer 2
plsql_v2_compatibility boolean FALSE
plsql_warnings string DISABLE:ALL
Ifplsql_native_library_dir
returns a NULL value, set the PL/SQL native compilation parameters at the system/session level as shown below:
eg.,SQL> alter system set plsql_native_library_dir = /opt/oracle/oradata/plsql_nativelib;
SQL> alter system set plsql_native_library_subdir_count = 150;
SQL> alter system set plsql_optimize_level = 2;
SQL> alter system set plsql_code_type = 'native';
SQL> alter session set plsql_compiler_flags = 'NATIVE', 'NON_DEBUG'; - Run dbmsupgnv.sql script
Use SQL*Plus to connect to the database as SYSDBA and run $ORACLE_HOME/rdbms/admin/dbmsupgnv.sql.% sqlplus / as sysdba
SQL> @$ORACLE_HOME/rdbms/admin/dbmsupgnv.sql
dbmsupgnv.sql is a script distributed by Oracle corporation along with Oracle 10g, to recompile all the PL/SQL modules in a database as NATIVE. Note that there is another script, dbmsupgin.sql, recompiles all the PL/SQL modules in a database as INTERPRETED.
This script takes about 5 minutes to complete. - Shut down and restart the database in normal mode
Shut down the database. Restart the database in normal mode by using SQL*Plus to connect to the database as SYSDBA and run the following command:% sqlplus / as sysdba
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup - Run utlirp.sql script
Use SQL*Plus to connect to the database as SYSDBA and run $ORACLE_HOME/rdbms/admin/utlirp.sql.SQL> @$ORACLE_HOME/rdbms/admin/utlirp.sql
Note:
Metalink document 312421.1, recommends running utlip.sql script for the regeneration of the compiled code. However another metalink document '272322.1 Difference between UTLRP.SQL - UTLIRP.SQL - UTLIP.SQL?' recommends running utlirp.sql script which would do both regeneration of compiled code of PL/SQL modules (which UTLIP.SQL does) and then recompiles all Invalid PL/SQL modules again (which UTLRP.SQL does).
[Updated 11/07/2009]
It appears that utlirp.sql requires the database be in upgrade mode. Since the database is already in normal mode at this point, the suggested step is to run utlip.sql script followed by utlrp.sql.
utlip.sql is the one that takes up a bulk of time in the native compilation of the PL/SQL packages. This step requires significant amount of free memory. Not having enough free memory slows down the processing significantly.
[/Updated]
Based on the speed of the underlying hardware, available memory and the efficiency of the compiler being used, regeneration of compiled code may take more than one day. So buy yourself a nice world tour package and have fun while Oracle and the compiler are busy generating the native code for you. - [Updated 11/07/09] Check all the invalid PL/SQL objects by running the following query as 'SYS' user.
SELECT o.OWNER, o.OBJECT_NAME, o.OBJECT_TYPE
FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s
WHERE o.OBJECT_NAME = s.NAME AND o.STATUS='INVALID';
Then recompile all the invalid objects using theALTER PACKAGE
command as shown below.ALTER PACKAGE <OWNER>.<OBJECT_NAME> COMPILE BODY REUSE SETTINGS;
For your convenience, the following SQL generates the necessary SQL statements to recompile the invalid objects.SELECT 'ALTER PACKAGE ' || o.OWNER || '.' || o.OBJECT_NAME || ' COMPILE BODY REUSE SETTINGS;'
FROM DBA_OBJECTS o, DBA_PLSQL_OBJECT_SETTINGS s
WHERE o.OBJECT_NAME = s.NAME AND o.STATUS='INVALID';
If the above command shows earnings such asWarning: Package Body altered with compilation errors.
, check the errors by runningSHOW ERRORS
, fix the error(s) and re-run the command. Repeat this process until the package compiles with no errors or warnings.
Reference:
Metalink document 312421.1 PL/SQL Native Compilation of Applications 11i on Oracle Database 10g Release 1 (10.1.0)
______________
Technorati tags:
Oracle | PL/SQL | Performance