Mandalika's scratchpad: Oracle Database 10g: PL/SQL Native Compilation

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:

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 2^nd and 3^rd 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 under plsql_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 by plsql_native_library_dir. Failure to create those directories may result in PLS-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 of plsql_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

If plsql_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 the ALTER 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 as Warning: Package Body altered with compilation errors., check the errors by running SHOW ERRORS, fix the error(s) and re-run the command. Repeat this process until the package compiles with no errors or warnings.

Mandalika's scratchpad

Pages

Monday, December 18, 2006

Oracle Database 10g: PL/SQL Native Compilation

No comments:

Post a Comment