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.
SQL> show error
Errors for PACKAGE APPS.AP_TAX_RECOVERY_PKG:
44/2 PLS-00920: parameter plsql_native_library_dir is not set
This is what I replied:
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
To learn more about native compilation you may refer to metalink notes 311971.1 and 269012.1.
Thanks and regards,
Rashmi from Sandeep's team wrote back:
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.
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):
Parameter type Integer
Default value 0
Modifiable ALTER SYSTEM
Range of values 0 to 232 - 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,
Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps