PL/SQL native compilation in Apps 11i and 12

I got a query from Sandeep today:

Do we need to use this parameter plsql_native_library_dir ? Can you please check this in production and see if we need to set this ?
Or do we need to introduce this post 10g upgrade ? Can you please suggest on this.

Issue:

SQL> show error
Errors for PACKAGE APPS.AP_TAX_RECOVERY_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
44/2 PLS-00920: parameter plsql_native_library_dir is not set

This is what I replied:

Hi Sandeep,

Even though native plsql compilation is available since 9i, it was not being used in our environment. It is optional and set through initialization parameter, plsql_code_type (new in 10g) to native. As per metalink note 216205.1, which describes the database initialization parameters for Apps 11i, whenever you are setting plsql_code_type=NATIVE you need to set all these variables:

plsql_code_type = NATIVE
plsql_native_library_dir = /prod11i/plsql_nativelib # Uncomment if using native PL/SQL
plsql_native_library_subdir_count = 149 # Uncomment if using native PL/SQL

Please refer to

Parameter Metalink Note
plsql_code_type 394422.1
plsql_native_library_dir 153370.1
plsql_native_library_subdir_count 153371.1

To learn more about native compilation you may refer to metalink notes 311971.1 and 269012.1.

Thanks and regards,

- Vikram

Rashmi from Sandeep's team wrote back:

Hi Vikram,

Thanks for your guidelines on this.

Question: How did we come up with plsql_native_library_subdir_count = 149?

Has any rule of thumb to be followed? Please let us know.

Thanks,

Rashmi

My reply:

Hi Rashmi

It is the default as per note 216205.1. Also as per note 444524.1, in ATG_PF.H RUP6, PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT has been AutoConfig-enabled to provide more flexibility. It is defined as %s_db_plsql_native_library_subdir_count% and has a default value of 149. Here is the explanation about this parameter from 10g DB reference guide (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams163.htm#REFRN10169):

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT

Property Description
Parameter type Integer
Default value 0
Modifiable ALTER SYSTEM
Range of values 0 to 2³² - 1 (max value represented by 32 bits)

PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT specifies the number of subdirectories created by the database administrator in the directory specified by PLSQL_NATIVE_LIBRARY_DIR.
When using the PL/SQL native compiler, Performance of file create/open operations is unacceptably slow if the number of files in a directory is very large. It is usually advisable to create subdirectories and use this parameter if the total number of different PL/SQL packages that may need to be compiled natively by all users of the database instance exceeds 10000.
The subdirectories should have names corresponding to zero-based decimal numbers, prefixed by d. For example, the database administrator can create 1000 subdirectories named d0, d1, ... d999, and set PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT to 1000.
I recommend that you set

plsql_native_library_dir = 10.2.0_ORACLE_HOME/plsql_nativelib
plsql_native_library_subdir_count = 149

and create mkdir $ORACLE_HOME/plsql_nativelib/d0,d1,d2…..d148.

Thanks and regards,

- Vikram