Mandalika's scratchpad [ Work blog @Oracle | My Music Compositions ]

Old Posts: 09.04  10.04  11.04  12.04  01.05  02.05  03.05  04.05  05.05  06.05  07.05  08.05  09.05  10.05  11.05  12.05  01.06  02.06  03.06  04.06  05.06  06.06  07.06  08.06  09.06  10.06  11.06  12.06  01.07  02.07  03.07  04.07  05.07  06.07  08.07  09.07  10.07  11.07  12.07  01.08  02.08  03.08  04.08  05.08  06.08  07.08  08.08  09.08  10.08  11.08  12.08  01.09  02.09  03.09  04.09  05.09  06.09  07.09  08.09  09.09  10.09  11.09  12.09  01.10  02.10  03.10  04.10  05.10  06.10  07.10  08.10  09.10  10.10  11.10  12.10  01.11  02.11  03.11  04.11  05.11  07.11  08.11  09.11  10.11  11.11  12.11  01.12  02.12  03.12  04.12  05.12  06.12  07.12  08.12  09.12  10.12  11.12  12.12  01.13  02.13  03.13  04.13  05.13  06.13  07.13  08.13  09.13  10.13  11.13  12.13  01.14  02.14  03.14  04.14  05.14  06.14  07.14  09.14  10.14  11.14  12.14  01.15  02.15  03.15  04.15  06.15  09.15  12.15  01.16  03.16  04.16  05.16  06.16  07.16  08.16  09.16  12.16  01.17  02.17  03.17  04.17  06.17  07.17  08.17  09.17  10.17  12.17  01.18  02.18  03.18  04.18  05.18  06.18  07.18  08.18  09.18  11.18  12.18  01.19  02.19  05.19  06.19  08.19  10.19  11.19  05.20  10.20  11.20  12.20 

Monday, December 18, 2006
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 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
  1. 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.

    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)

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

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

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

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

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

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

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


    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.

    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.

  7. [Updated 11/07/09] Check all the invalid PL/SQL objects by running the following query as 'SYS' user.


    Then recompile all the invalid objects using the ALTER PACKAGE command as shown below.

    For your convenience, the following SQL generates the necessary SQL statements to recompile the invalid objects.

    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.

Metalink document 312421.1 PL/SQL Native Compilation of Applications 11i on Oracle Database 10g Release 1 (10.1.0)

Technorati tags:
| |

Friday, December 01, 2006
Solaris: Different page sizes for Oracle PGA

Bad TLB performance?

If you notice more dTLB misses while running Oracle database on a Solaris box, disable the real-free memory manager for PGA (Program Global Area) by setting the parameter _use_realfree_heap=FALSE in Oracle database initialization file (init.ora); and then use MPSS (Multiple Page Size Support) to set the desired page size for the heap with environment variable MPSSHEAP=<pagesize>. Note that has to be pre-loaded for the env variable MPSSHEAP to be effective.

Relevant steps are as follows:
  1. init.ora:


  2. In a shell:

    % LD_PRELOAD_64=/usr/lib/sparcv9/ <- assuming 64-bit Oracle
    % MPSSHEAP=4M <- assuming 4M pages provide good performance
    % export LD_PRELOAD_64 MPSSHEAP
    % sqlplus / as sysdba
    SQL> startup <- start up the database

Experiment with different page sizes supported by the underlying hardware platform, and measure the dTLB performance with trapstat tool. In case of performance degradation, simply revoke the changes.

Related information:

The real-free memory manager for PGA is enabled by default; and Oracle uses mmap()/munmap() calls {instead of malloc() and brk()} to allocate/deallocate anonymous memory for PGA. If _use_realfree_heap is set to TRUE, the memory for working areas (CGA/UGA) will be allocated on independent heaps i.e., UGA and CGA will not be a part of PGA.

You can find an interesting write-up on the advantages/disadvantages of real-free memory manager for PGA in Shrinking PGA of snp processes thread.


In general Oracle doesn't recommend using undocumented (hidden) parameters in production environments. Consult Oracle support before using any of the hidden parameters you find interesting.

To get the list of undocumented parameters, run the following script against your database instance.


set lines 750
set pages 1000
COL name FORMAT a55
COL value FORMAT a15
COL description FORMAT a150


a.ksppinm NAME,
b.ksppstvl VALUE,
a.ksppdesc DESCRIPTION


sys.x$ksppi a,
sys.x$ksppcv b


a.indx = b.indx


a.ksppinm like '\_%' escape '\'

order by


Ravindra Talashikar

Technorati tags:
| |


This page is powered by Blogger. Isn't yours?